This discussion is archived
1 Reply Latest reply: Feb 11, 2012 1:29 PM by Frank Kulash RSS

Nested Select

851394 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points