Re: Updating single row/column in table A with values from single column but multiple rows in table B

On 07 May 2012 10:25, fred clemens wrote:

I am trying to construct an UPDATE statement to update a single
column in a single row in table A with concatenated values from a
single column in multiple rows in table B. In googling the subject,
I have found a concept in MYSQL called Group_Concat that seems to
accomplish that task, although I only found examples where it was
used in Select statements, not Update statements. I looked in the
DB2 for i SQL reference and could find nothing about this concept.
Is there a way to accomplish this using DB2 for I SQL?? Below is
one of my attempts to try to use the Group Concat:

UPDATE MLPFLKSCD A
SET A. FSADIN =
GROUP_CONCAT(
SELECT B.FXDATA
FROM MLPFLOCKAD B
WHERE A.FSCMP=B.FXCMP AND A.FSLOC=B.FXLOC
AND A.FSFARMNO=B.FXFARMNO AND A.FSBARNID=B.FXBARNID
AND A.FSYEAR=B.FXYEAR AND A.FSNUM=B.FXNUM
AND A.FSPENID=B.FXPENID AND A.FSSEX=B.FXSEX
AND A.FSTSLT=B.FXTSLT AND A.FSSTDT=B.FXSDAT
)

A function specific to the task, can replace the more generic effect of that Group_Concat [aggregate] function. For example:

<code>

create function MLPFLOCKAD_CAT_FXDATA
( FSCMP ... , FSLOC ...
, FSFARMNO ... , FSBARNID ...
, FSYEAR ... , FSNUM ...
, FSPENID ... , FSSEX ...
, FSTSLT ... , FSSTDT ...
) returns varchar( ... )
language SQL
...
begin
declare RtnCat varchar ( ... ) default '';
For Cat as CatFXdata cursor for
SELECT B.FXDATA
FROM MLPFLOCKAD B
WHERE FSCMP=B.FXCMP AND FSLOC=B.FXLOC
AND FSFARMNO=B.FXFARMNO AND FSBARNID=B.FXBARNID
AND FSYEAR=B.FXYEAR AND FSNUM=B.FXNUM
AND FSPENID=B.FXPENID AND FSSEX=B.FXSEX
AND FSTSLT=B.FXTSLT AND FSSTDT=B.FXSDAT
Do
/* Assumed: no FXDATA will have colon as suffix */
set RtnCat = RtnCat CONCAT Cat.FXDATA CONCAT ':' ;
End For;
Return Trim( trailing ':' from RtnCat) ;
End

</code>

The function could then be invoked in the given update request:

UPDATE MLPFLKSCD A
SET A.FSADIN =
MLPFLOCKAD_CAT_FXDATA(
FSCMP , FSLOC
, FSFARMNO , FSBARNID
, FSYEAR , FSNUM
, FSPENID , FSSEX
, FSTSLT , FSSTDT )
-- WHERE ...

I use an almost identical function source, a routine called PARMLIST, to generate a string describing the parameter declarations from the SYSPARMS data [from SYSROUTINE data]. So if I request to SELECT PARMLIST('PARMLIST','MyLibName') FROM SYSIBM/SYSDUMMY1, for example to get the parameter list of just that one function, the result\report is:

....+....1....+....2....+....3....+....4....+....5....+....6. ...
PARMLIST
(CHARACTER VARYING(128),CHARACTER VARYING(128),CHARACTER VARYING(1000))
******** End of data ********

Regards, Chuck