I have 2 tables (TABLE_1, TABLE_2) and I need to update the 'STATUS' field in TABLE_1 with the 'STATUS_LABEL' field in TABLE_2, where:
I have tried this so far however when I try to run the statement in Oracle Toad, it takes ages to load and doesnt return any errors so i'm not sure if I've done something wrong or if I'm missing something:
UPDATE TABLE_1
SET TABLE_1.STATUS=(SELECT TABLE_2.STATUS_LABEL
FROM TABLE_2
WHERE TABLE_2.ID = TABLE_1.ID
AND TABLE_2.STATUS_LABEL <> TABLE_1.STATUS)
WHERE TABLE_1.CATEGORY = 'SPARKLA'
AND EXISTS (SELECT 1 FROM TABLE_2 WHERE TABLE_2.ID =TABLE_1.ID);
Please could you kindly look at my code and guide me what to do.
Thank you.
The where clause for the exists should be the same as for the select used for update so as to avoid updating to null in case that select finds nothing. Thus< I think it should be like below:
UPDATE TABLE_1
SET TABLE_1.STATUS=(SELECT TABLE_2.STATUS_LABEL
FROM TABLE_2
WHERE TABLE_2.ID = TABLE_1.ID
AND TABLE_2.STATUS_LABEL <> TABLE_1.STATUS)
WHERE TABLE_1.CATEGORY = 'SPARKLA'
AND EXISTS (SELECT 1
FROM TABLE_2
WHERE TABLE_2.ID = TABLE_1.ID
AND TABLE_2.STATUS_LABEL <> TABLE_1.STATUS);