Update DB2 table With a Join
Acknowledging that DB2 doesn’t allow JOINS in the update statement, utilizing MERGE INTO is the key to achieve what we want.
Let’s say we want to update a table named CSI_STUDENTS, in particular the NAME field, with the value that comes from table CSI_APPLICANTS, field STUDENT_NAME, and the common field they have is called SID (Student ID)
This is the simple query we’d use:
|
1 2 3 4 5 6 7 |
MERGE INTO CSI_STUDENTS A USING (SELECT SID, STUDENT_NAME FROM CSI_APPLICANTS WHERE STATE IN ('ACCAPTED')) B ON B.SID = A.SID WHEN MATCHED THEN UPDATE SET A.NAME = B.STUDENT_NAME |
The query above will ensure only the NAMEs of students are updated which have been marked as “ACCEPTED” in the CSI_APPLICANTS table.
