1 Reply Latest reply: Feb 11, 2012 3:29 PM by Frank Kulash RSS

    Nested Select

    851394
      I have the following query:

      update table1
      field1=(select * from table2 where column1='y'),
      field2=(select * from table2 where column1='n')
      where table1.col='12qw'; //This indexed table results in 1million records for this where clause

      The query runs properly but the execution plan shows 2 full table scan for table2 one for column1='y' and another one for column2='n'.

      Is there any way that I can make this query work with only one table scan for table 2?
        • 1. Re: Nested Select
          Frank Kulash
          Hi,
          848391 wrote:
          I have the following query:

          update table1
          field1=(select * from table2 ...
          So table2 only has one column?

          You can get both columns from table2 at the same time, using MERGE:
          MERGE INTO        table1  dst
          USING  (
                     SELECT  MIN (CASE WHEN column1 = 'y' THEN column1 END)     AS field1
                  ,        MIN (CASE WHEN column1 = 'n' THEN column1 END)     AS field2
                  FROM        table2
                  WHERE   column1     IN ('y', 'n')
                 )                   src
          ON     (dst.col     = '12qw')
          WHEN MATCHED THEN UPDATE
          SET    dst.field1 = src.field1
          ,      dst.field2 = src.field2
          ;
           

          I hope this answers your question.
          If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
          Since you're asking about a DML statement, the sample data will be the contents of the tables before the DML, and the results will be state of the changed table when everything is finished.
          Explain, using specific examples, how you get those results from that data.
          Always say which version of Oracle you're using.