Update From statements in DB2 1

[putts]

I'm guessing these aren't allowed.
The following is my example....

UPDATE
FI_SUM_WF_AGENT_REL SET FI_AGY_CD = CL_AGY_CD, FI_POL_ID = AF_TITLE,
FI_NAME = HOLDER_NAME,FI_STATUS = STATUS_DESC,FI_WF_STOP = WST_STOP_TS
FROM
select DISTINCT CL_AGY_CD,,AF_TITLE,
CASE
WHEN CL_DBA_NM_SRCH IS NOT NULL THEN CL_DBA_NM_SRCH
ELSE CONCAT(CONCAT(CL_LAST_NM_SRCH,', '),CL_FIRST_NM_SRCH)
END AS HOLDER_NAME,
CASE
WHEN D.WS_INSTANCE_ID IS NOT NULL THEN 'Terminated'
WHEN C.WSA_ASSIGNED_NM IS NOT NULL THEN C.WSA_ASSIGNED_NM
WHEN dat_data_txt IS NULL THEN 'Completed'
WHEN posstr(dat_data_txt,'Accept') > 0 THEN 'Accepted'
WHEN posstr(dat_data_txt,'Reject') > 0 THEN 'Rejected'
ELSE 'Error in Current Step'
END AS STATUS_DESC,A.WS_INSTANCE_ID AS FI_WF_INSTANCE_ID, WST_STOP_TS AS FI_WF_STOP
from WF_START A
LEFT OUTER JOIN WF_STOP B ON A.WS_INSTANCE_ID = B.WS_INSTANCE_ID
LEFT OUTER JOIN (SELECT WS_INSTANCE_ID,WSA_ASSIGNED_NM FROM WF_STEP_ACTIVITY WHERE WSA_STATUS_CD = 'S') C ON A.WS_INSTANCE_ID = C.WS_INSTANCE_ID
LEFT OUTER JOIN (SELECT WS_INSTANCE_ID,WSA_ASSIGNED_NM FROM WF_STEP_ACTIVITY WHERE WSA_STATUS_CD = 'T') D ON A.WS_INSTANCE_ID = D.WS_INSTANCE_ID
INNER JOIN WF_ITEM_REL E ON E.WS_INSTANCE_ID = A.WS_INSTANCE_ID
INNER JOIN FOLDER_OBJECT_REL F ON F.MD_ID = E.WR_ITEM_ID
LEFT OUTER JOIN CABINET_FOLDER_REL G ON ((F.CT_ID = G.CF_FOLDER_ID) OR (F.CT_ID = G.CF_PARENT_ID))
LEFT OUTER JOIN APP_FOLDER_TAB H ON (H.AF_FOLDERID = G.CF_CABINET_ID OR F.CT_ID = H.AF_FOLDERID)
LEFT OUTER JOIN FMI_CL_TAB I ON I.CL_POL_ID = H.AF_TITLE
LEFT OUTER JOIN app_data_tab J ON (APP_TYPE = 'FMICL' and dat_key_id = H.AF_TITLE and dat_data_NM = 'AcceptRejectTab')
WHERE
A.WS_INSTANCE_ID = FI_SUM_WF_AGENT_REL.WS_INSTANCE_ID

Now, that select query in itself takes several minutes so I don't want to duplicate it several times over for every field.

Any help/confirmation on this would be greatly appreciated

 

[blom0344]

Generally speaking, syntax would be something along this:

Update table
Set <field> = .......
Where .......

I think you are quite a bit off the mark?

Ties Blom
Information analyst

 

[MarcLodge]

Hi Putts,
Try this syntax:

UPDATE FI_SUM_WF_AGENT_REL
SET (FI_AGY_CD
,FI_POL_ID
,FI_NAME
,FI_STATUS
,FI_WF_STOP) = (SELECT CL_AGY_CD
,AF_TITLE
,HOLDER_NAME
,STATUS_DESC
,WST_STOP_TS
from blah blah blah)

Add WHERE clauses where required, and incorporate your existing inner select into the 2nd select in order to give you the results you require.

Page 58 of Graham Birchall's excellent cookbook gives a much better example and can be found here:

http://ourworld.compuserve.com/homepages/graeme_birchall/DB2V82CK.PDF

Hope this helps,

Marc

 

[putts]

Thank you, Marc.....that was it!


UPDATE FI_SUM_WF_AGENT_REL SET (FI_AGY_CD,FI_POL_ID,FI_NAME,FI_STATUS,FI_WF_STOP,FI_WF_INSTANCE_ID) =
(select DISTINCT CL_AGY_CD,AF_TITLE,
CASE
WHEN CL_DBA_NM_SRCH IS NOT NULL THEN CL_DBA_NM_SRCH
ELSE CONCAT(CONCAT(CL_LAST_NM_SRCH,', '),CL_FIRST_NM_SRCH)
END AS FI_NAME,
CASE
WHEN D.WS_INSTANCE_ID IS NOT NULL THEN 'Terminated'
WHEN C.WSA_ASSIGNED_NM IS NOT NULL THEN C.WSA_ASSIGNED_NM
WHEN dat_data_txt IS NULL THEN 'Completed'
WHEN posstr(dat_data_txt,'Accept') > 0 THEN 'Accepted'
WHEN posstr(dat_data_txt,'Reject') > 0 THEN 'Rejected'
ELSE 'Error in Current Step'
END AS STATUS_DESC, WST_STOP_TS,A.WS_INSTANCE_ID
from WF_START A
LEFT OUTER JOIN WF_STOP B ON A.WS_INSTANCE_ID = B.WS_INSTANCE_ID
LEFT OUTER JOIN (SELECT WS_INSTANCE_ID,WSA_ASSIGNED_NM FROM WF_STEP_ACTIVITY WHERE WSA_STATUS_CD = 'S') C ON A.WS_INSTANCE_ID = C.WS_INSTANCE_ID
LEFT OUTER JOIN (SELECT WS_INSTANCE_ID,WSA_ASSIGNED_NM FROM WF_STEP_ACTIVITY WHERE WSA_STATUS_CD = 'T') D ON A.WS_INSTANCE_ID = D.WS_INSTANCE_ID
INNER JOIN WF_ITEM_REL E ON E.WS_INSTANCE_ID = A.WS_INSTANCE_ID
INNER JOIN FOLDER_OBJECT_REL F ON F.MD_ID = E.WR_ITEM_ID
LEFT OUTER JOIN CABINET_FOLDER_REL G ON ((F.CT_ID = G.CF_FOLDER_ID) OR (F.CT_ID = G.CF_PARENT_ID))
LEFT OUTER JOIN APP_FOLDER_TAB H ON (H.AF_FOLDERID = G.CF_CABINET_ID OR F.CT_ID = H.AF_FOLDERID)
LEFT OUTER JOIN FMI_CL_TAB I ON I.CL_POL_ID = H.AF_TITLE
LEFT OUTER JOIN app_data_tab J ON (APP_TYPE = 'FMICL' and dat_key_id = H.AF_TITLE and dat_data_NM = 'AcceptRejectTab')
WHERE FI_SUM_WF_AGENT_REL.FI_WF_INSTANCE_ID = A.WS_INSTANCE_ID)

That's the statement as it should look.

To anyone out there who is used to the MS SQL syntax (such as myself) of the UPDATE FROM command, this is an equalivent to that it's just a smidge bit different.

One Note to watch : if you're trying to do a joined relationship between the table being updated and the Select doing the updating, that needs to go inside the Values clause. In my case, the where clause within the Values clause.