CODEWITHSUNDEEP
sqloracle-databaseoracle11g
user1720827
user1720827

Reputation: 147

Alternative for correlated update of Oracle

How can I re-write this query to sample update. Can someone decode me for this step by step. Is the whole table1 getting updated here/

 UPDATE
    (SELECT 
    A.COLUMN1 A_COLUMN1,
    B.COLUMN2 B_COLUMN2
    FROM TABLE1 A,TABLE2 B
    WHERE A.COLUMN3=B.COLUMN3 AND A.COLUMN4=B.COLUMN4)
    SET A_COLUMN1=B_COLUMN2;

Upvotes: 0

Views: 604

Answers (2)

user1720827
user1720827

Reputation: 147

This way it touches only the common elements

UPDATE TABLE1
    SET A_COLUMN1 = (SELECT B.B_COLUMN2
                     FROM TABLE2 B
                     WHERE A.COLUMN3 = B.COLUMN3 AND A.COLUMN4 = B.COLUMN4
                    )
   WHERE EXISTS (SELECT B.B_COLUMN2
                     FROM TABLE2 B
                     WHERE A.COLUMN3 = B.COLUMN3 AND A.COLUMN4 = B.COLUMN4);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270613

Let me answer the question.

The subquery is using an inner join. Hence, the subquery will filter out rows that don't match the join conditions in the two tables. This happens before the update.

Hence, not all the rows get updated. If you want to update all rows, use a left join or:

UPDATE TABLE1
    SET A_COLUMN1 = (SELECT B.B_COLUMN2
                     FROM TABLE2 B
                     WHERE A.COLUMN3 = B.COLUMN3 AND A.COLUMN4 = B.COLUMN4
                    );

Upvotes: 1

Related Questions