SQL update from one Table to another based on a ID match IN db2

db2 sql-update db2-luw correlated-subquery

Agent Mahone · Apr 25, 2014 · Viewed 16.5k times · Source

The Query below is suited in SQL sErver. But in DB2 it does not give results:

Error is  SQLCODE = -199, ERROR:  ILLEGAL USE OF KEYWORD FROM.

Query:

UPDATE
     Sales_Import
 SET
    Sales_Import.AccountNumber = RAN.AccountNumber
FROM
Sales_Import SI
INNER JOIN
RetrieveAccountNumber RAN
ON 
SI.LeadID = RAN.LeadID

Can someone please clarify the differences b/w DB2 and SQL queries.

Answer

I'm pretty sure (although I've not used DB2 in a while) that DB2 still does not support joins in update statements, so you'll need to use MERGE;

Something like this (freehanding it since I don't have DB2 available, so may be slightly off);

MERGE INTO Sales_Import si
USING (SELECT AccountNumber, LeadID FROM RetrieveAccountNumber) ra
ON (si.LeadID = ra.LeadID)
WHEN MATCHED THEN
 UPDATE SET AccountNumber = ra.AccountNumber

SQL update from one Table to another based on a ID match IN db2

Answer

Related questions