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:
MERGE INTO CSI_STUDENTS A
USING (SELECT SID, STUDENT_NAME
WHERE STATE IN ('ACCAPTED')) B
ON B.SID = A.SID
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.