Ashish Ashish - 3 months ago 10
SQL Question

Updating the same column from different columns of another table

I am using the following 3 queries to update the status based on the value of 3 different column from another table.

UPDATE NUMBER_TABLE SET STATUS='X' WHERE STATUS='P' AND NUMBER IN
(SELECT NVL(CONTACT_NO,'XXXXXXXXXX') FROM TMP_NUMBER_FOUND WHERE STATUS='P');

UPDATE NUMBER_TABLE SET STATUS='X' WHERE STATUS='P' AND NUMBER IN
(SELECT NVL(HOME_NO,'XXXXXXXXXX') FROM TMP_NUMBER_FOUND WHERE STATUS='P');

UPDATE NUMBER_TABLE SET STATUS='X' WHERE STATUS='P' AND NUMBER IN
(SELECT NVL(WORK_NO,'XXXXXXXXXX') FROM TMP_NUMBER_FOUND WHERE STATUS='P');


All 3 queries are doing full table scan. Can someone suggest a better way of merging 3 queries into one

Answer

Something like this should work.

NOTE: You said you use those three queries to update... That is not true. Your queries would not work as written; NUMBER is a reserved Oracle keyword, so you would get compilation errors. When you post, please be very careful what you say. The query I offer below as a solution will also not work, for the same reason. Use your actual column names.

UPDATE NUMBER_TABLE n SET STATUS='X'  WHERE STATUS='P' AND EXISTS
   ( SELECT 1 FROM TMP_NUMBER_FOUND t where n.NUMBER = NVL(t.CONTACT_NO, 'XXXXXXXXXX')
                                      or    n.NUMBER = NVL(t.HOME_NO   , 'XXXXXXXXXX')
                                      or    n.NUMBER = NVL(t.WORK_NO   , 'XXXXXXXXXX')
   )
;