Good example/document for Correlated subquery


You can check the following page for Correlated Subqueries

Especially update query

https://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=%2Fsqlp%2Frbafyexsub4.htm

basic syntax

UPDATE TABLE_NAME X
SET FIELD=(SELECT Y.ANYFIELD
FROM ANOTHERTABLE Y
WHERE X.ID= Y.FID)
WHERE X.ID IN (SELECT FID
FROM ANOTHERTABLE)

UPDATE EMPLOYEE X
SET NEWSALARY=( SELECT Y.NEWSALARY
FROM SALARIES Y
WHERE X.EMPID = Y.EMPID)
WHERE X.EMPID IN ( SELECT EMPID
FROM SALARIES)

Advertisements