Bob Bob - 5 months ago 21
SQL Question

Perform UPDATE based on a JOIN in DB2

I have 2 tables:

TableA

POA LIA SKUA CODEA
10000001 1 L002022 A002
10000001 2 L002324 A002
10000002 1 L002022 G002
10000002 2 L002324 G002
10000002 3 L002526 G002
10000002 4 L002022 G002
10000003 1 L003334 G002
10000003 2 L003537 G002
10000003 3 L003839 G002
10000003 4 L003334 G002


TableB

POB LIB SKUB CODEB TYPE
10000001 1 L002022 AA
10000001 2 L002324 BC
10000002 1 L002022 DE
10000002 2 L002324 FG
10000002 3 L002526 AA
10000002 4 L002022 DE
10000003 1 L003334 TG
10000003 2 L003537 VF
10000003 3 L003839 UY
10000003 4 L003334 FR


I need to update
TableB
namely column
CodeB
(which is currently blank) to be the exact of TableA:

POB LIB SKUB CODEB TYPE
10000001 1 L002022 A002 AA
10000001 2 L002324 A002 BC
10000002 1 L002022 G002 DE
10000002 2 L002324 G002 FG
10000002 3 L002526 G002 AA
10000002 4 L002022 G002 DE
10000003 1 L003334 G002 TG
10000003 2 L003537 G002 VF
10000003 3 L003839 G002 UY
10000003 4 L003334 G002 FR


How can I do that in a simple SQL update statement as I have thousands of such missing records?

Answer

This may or may not work in DB2, but I darn sure tried. Read: "trying" means searching the internet for "DB2 update based on join".

UPDATE TableB
SET CODEB = (
   SELECT CODEA
   FROM TableA
   WHERE
      TableA.POA = B.POB
      AND TableA.LIA = TableB.LIB
      AND TableA.SKUA = TableB.SKUB
)
WHERE EXISTS (
   SELECT *
   FROM TableA
   WHERE
      TableA.POA = B.POB
      AND TableA.LIA = TableB.LIB
      AND TableA.SKUA = TableB.SKUB
);

You should also be able to do something close to this:

MERGE INTO TableB B
USING TableA A
   ON A.POA = B.POB
   AND A.LIA = B.LIB
   AND A.SKUA = B.SKUB
WHEN MATCHED
   THEN UPDATE SET B.CODEB = A.CODEA;

You must ensure there is only one source row for each updated row. If PO, LI, SKU is not guaranteed unique, you could try using a subquery having a DISTINCT or a GROUP BY to fix it.