Update record in table A if it exists in table B-CodePudding

I have a temporary table, Session.FINAL, that holds customer IDs and other related info about the customer:

DECLARE  GLOBAL TEMPORARY TABLE SESSION.FINAL
   (ID        CHAR(30)
   ,NM1       CHAR(30)
   ,SCNDY     INTEGER
   ,NM2       CHAR(30)
   ,FRMT      CHAR(10))
;

Last year, some customers were assigned a secondary ID. For example, Jim originally had customer ID 101 and last year, he was assigned a secondary ID of 103822. The problem is that in the Session.FINAL table, ID 101 and 103822 are separate rows even though they belong to the same customer.

What I want to do is update the ID field in Session.FINAL to store both the primary ID and the renumbered ID. So in Jim's case, I would like his ID to be "101-103822".

I have another temporary table, Session.RENUMBER, that holds a customer's primary ID and secondary ID. For example, Jim's RE_PRIM_ID = 101 and RE_SCNDY_ID = 103822:

DECLARE GLOBAL TEMPORARY TABLE SESSION.RENUMBER
AS (SELECT DISTINCT
 RE_PRIM_ID
,RE_SCNDY_ID
FROM RWDCCC#1.TUEAL
FETCH FIRST ROW ONLY)
DEFINITION ONLY
;

This is my query to update Session.FINAL:

UPDATE SESSION.FINAL                  FIN
SET FIN.ID = CAST(FIN.ID AS CHAR(5)) || '-' || CAST(RENUM.RE_SCNDY_ID AS CHAR(5))
WHERE 1=1
AND EXISTS  --If Session.FINAL ID exists in Session.RENUMBER, the customer's ID has been renumbered*
(SELECT 1
FROM SESSION.RENUMBER                   RENUM
WHERE FIN.ID = RENUM.RE_PRIM_ID)
;

This query is giving me an error that says RENUM.RE_SCNDY is not valid in this context. It seems like I can't reference the Session.RENUMBER table since it's in it's own SELECT statement. Where am I going wrong here?

CodePudding user response：

As DB2 doesn't allow Joins you can solve ypour problem by using a subquery

i adapted your tabkes so tat it would run as i don't have your database

CREATE   TABLE FINAL
   (ID        CHAR(30)
   ,NM1       CHAR(30)
   ,SCNDY     INTEGER
   ,NM2       CHAR(30)
   ,FRMT      CHAR(10))
;

CREATE   TABLE RENUMBER
(
 RE_PRIM_ID INT
,RE_SCNDY_ID INT
);

update
  FINAL FIN
  SET FIN.ID = CAST(FIN.ID AS CHAR(5)) || '-' || CAST(
(SELECT RENUM.RE_SCNDY_ID
FROM RENUMBER                   RENUM
WHERE FIN.ID = RENUM.RE_PRIM_ID) AS CHAR(5))

db<>fiddle here

CodePudding user response：

Depending on your Db2 platform and version one of the following:

UPDATE SESSION.FINAL F
SET ID = RTRIM (CHAR (R.RE_PRIM_ID)) || '-' || RTRIM (CHAR (R.RE_SCNDY_ID))
FROM SESSION.RENUMBER R
WHERE F.ID
--IN (R.RE_PRIM_ID, R.RE_SCNDY_ID) 
= R.RE_PRIM_ID
;

MERGE INTO SESSION.FINAL F
USING SESSION.RENUMBER R ON F.ID
--IN (R.RE_PRIM_ID, R.RE_SCNDY_ID) 
= R.RE_PRIM_ID
WHEN MATCHED THEN UPDATE
SET ID = RTRIM (CHAR (R.RE_PRIM_ID)) || '-' || RTRIM (CHAR (R.RE_SCNDY_ID))
;