Update Query using Inner Join and Case
Hello All – I am fairly new to this kind of programming so apologies in advance.
I am testing some updates we will need to run on our PeopleSoft DB through TOAD. I have one table that needs to be updated, but for multiple rows with different sets of values.
For example, TableA lists line info, and TableB is header info. I need to update a rows from TableA based on criteria from both TableA and TableB.
Because this includes multiple rows with different values, I am trying to use the CASE method. I’m not sure if this is correct. Below is the query I have come up with so far, but I get an error about not ending properly.
I really have 2 concerns…
-I see that the CASE statement uses ELSE. I do not want any records not specifically mentioned in my CASE to be updated. If I leave out the ELSE argument, will only the records I specify with the WHEN, THEN be updated and everything else ignored?
-Can the CASE statement be used with an Inner Join like this?
Any suggestions would be helpful…thanks!
UPDATE sysadm.TableA A
SET A.Field1 = CASE A.Field1
WHEN "OldValue1" THEN "NewValue1"
WHEN "OldValue2" THEN "NewValue2"
WHEN "OldValue3" THEN "NewValue3"
END
INNER JOIN sysadm.TableB B ON (A.PKCriteria1 = B.PKCriteria1 A.PKCriteria2 = B.PKCriteria2)
WHERE A.Critera = "X"
AND B.DATE >= TO_DATE('2010-06-01', 'YYYY-MM-DD')
AND B.Criteria = 'D'
