0

Which is better in terms of performance?

UPDATE 
(SELECT table1.value as OLD, table2.CODE as NEW
 FROM table1
 INNER JOIN table2
 ON table1.value = table2.DESC
 WHERE table1.UPDATETYPE='blah'
) t
SET t.OLD = t.NEW;


UPDATE table1 
SET t.value = 
(SELECT CODE
 FROM table2
 WHERE table1.value = table2.DESC
) t
WHERE t.UPDATETYPE='blah';

I am looking at the answers from origial questsion here: Update statement with inner join on Oracle

Community
  • 1
  • 1
stoniel2
  • 93
  • 1
  • 2
  • 9
  • Run `EXPLAIN PLAN FOR ...` the first, and then for the second command, and then compare their plans. You can find more on this topic in this link: https://docs.oracle.com/cd/B10501_01/server.920/a96533/ex_plan.htm – krokodilko Mar 25 '16 at 17:23
  • The two queries don't actually do the same thing in general... The first only updates those rows where there is a matching row in `table2`. The second updates every row in `table1` with a given `updatetype` whether or not there is a match. That's why the answer you link to has the `WHERE EXISTS` predicate. – Justin Cave Mar 25 '16 at 17:45

0 Answers0