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

Question

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.

mustaccio · Answer 1 · 2015-07-07T19:57:01.237

8

DB2 does indeed support joins in an UPDATE statement, only not the way you think -- DB2 follows the SQL ANSI standard:

UPDATE
     Sales_Import SI
 SET
    Sales_Import.AccountNumber = (
      SELECT 
        RAN.AccountNumber
      FROM
        RetrieveAccountNumber RAN
      WHERE  
        SI.LeadID = RAN.LeadID
    )

The above assumes that LeadID uniquely identifies records in RetrieveAccountNumber, otherwise you will get an error because the subquery would return more than one row.

Edit:

To address comments below, if no matching record in RetrieveAccountNumber can be found, Sales_Import.AccountNumber will be set to null. If this is undesirable, one could use COALESCE() to assign a default value.

edited Jul 07 '15 at 19:57

answered Apr 25 '14 at 11:16

mustaccio

18,234
16
48
57

This also assumes that there is a row in RetrieveAccountNumber for every LeadID in Sales_Import. So there must be one, and only one matching row in RetrieveAccountNumber. – WarrenT Apr 29 '14 at 14:14
I think it will try to update the value to null if it's not found in the joined table, which may or may not be the desired behavior. – Scott McIntyre Jul 07 '15 at 19:41
This does not allow to reference the current row of the subquery in the where clause without using copy-paste and other ugly things... – Chucky Apr 10 '18 at 12:26

score 6 · Accepted Answer · answered Apr 25 '14 at 06:01

6

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

answered Apr 25 '14 at 06:01

Joachim Isaksson

176,943
25
281
294

1

Better yet, only update when the value will change. `WHEN MATCHED and AccountNumber <> ra.AccountNumber THEN...` – WarrenT Apr 29 '14 at 14:08

score 1 · Answer 3 · edited Mar 22 '17 at 07:53

1

UPDATE USERS.A A SET A.NAME=(SELECT B.NAME FROM USERS.B B  WHERE A.ID=B.ID );

edited Mar 22 '17 at 07:53

Petter Friberg

21,252
9
60
109

answered Mar 22 '17 at 05:25

Mohit

129
2
3
13

score 0 · Answer 4 · answered Apr 29 '14 at 14:24

If you want to use UPDATE rather than MERGE, you want to process only the matching records where the value will change.

UPDATE Sales_Import SI
  SET SI.AccountNumber = 
        (SELECT RAN.AccountNumber
           FROM RetrieveAccountNumber RAN
           WHERE SI.LeadID = RAN.LeadID
           FETCH FIRST ROW ONLY
        )
  WHERE SI.LeadID IN
        (SELECT S2.LeadID
           FROM Sales_Import S2
           JOIN RetrieveAccountNumber R2
             ON S2.LeadID = R2.LeadID
           WHERE S2.AccountNumber <> R2.RetrieveAccountNumber 
        )

S2.AccountNumber <> R2.RetrieveAccountNumber work only if S2.AccountNumber is not null and R2.RetrieveAccountNumber is not null. If you want update with a null value you must remove this row — Esperento57, Nov 15 '16 at 18:55

score 0 · Answer 5 · answered Nov 15 '16 at 18:54

response to your question

UPDATE Sales_Import f1
SET f1.AccountNumber = 
(
 SELECT f2.AccountNumber
 FROM RetrieveAccountNumber f2
 WHERE f1.LeadID = f2.LeadID
 FETCH FIRST ROW ONLY
)
WHERE exists
(
SELECT * FROM RetrieveAccountNumber f2
 WHERE f1.LeadID = f2.LeadID 
)

template methode

update table1 f1
set (f1.field1, f1.field2, f1.field3, f1.field4)=
(
select f2.field1, f2.field2, f2.field3, 'CONSTVALUE'
from table2 f2
where (f1.key1, f1.key2)=(f2.key1, f2.key2) 
)
where exists 
(
select * from table2 f2
where (f1.key1, f1.key2)=(f2.key1, f2.key2)
)

score 0 · Answer 6 · answered May 28 '19 at 14:33

In case someone else want to update a table with the SUM() of values of another Table on DB2. I was running into many issues with joins and finally a simple SELECT in the UPDATE solved it. Thx to @mustaccio

TABLE_A
ID......|TOTAL...|...
1
5
6

TABLE_B
ID......|REF_ID...|QUANTITY|...
1        5         20
2        1         25
3        1         3

SQL Statement:  
UPDATE TABLE_A ta
SET ta.TOTAL = (
    SELECT SUM(tb.QUANTITY)
    FROM TABLE_B tb
    WHERE tb.REF_ID = ta.ID
)

RESULT
TABLE_A
ID......|TOTAL...|...
1        28
5        20
6        NULL

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

6 Answers6

Linked

Related