|
View previous topic :: View next topic
|
| Author |
Message |
bachi234
New User
Joined: 15 May 2007
Posts: 6
Location: VA
|
|
|
|
EXperts, Could you please share your thought on what i should be adding in order to make this update??
For all the resulting rows that i get from this sql i want to update ICS.CPT with DCS.CPT.
| Code: |
SELECT BP.LBC AS BP_CASE
,ILB.CN AS ILB_CASE
,DTL.LBCN AS DTL_CASE
,BCS.CPT AS BP_PTD
,ICS.CPT AS ILB_PTD
,DCS.CPT AS DTL_PTD
FROM DB2TEST.GLH BP
,DB2TEST.GLH ILB
,DB2TEST.GLH DTL
,DB2TEST.CM BCS
,DB2TEST.CM ICS
,DB2TEST.CM DCS
WHERE BP.OC = 'CV'
AND BP.CUI = 'BP'
AND BP.LBS = 'A'
AND ILB.BLC = BP.LBC
AND ILB.CUI = 'IB'
AND ILB.LBS = 'A'
AND DTL.PCN = ILB.LCN
AND DTL.CUI = 'DC'
AND DTL.LBS = 'A'
AND BCS.CN = BP.LCN
AND ICS.CN = ILB.LCN
AND DCS.CN = DTL.LCM
AND NOT ICS.CPT = DCS.CPT |
I tried using the below query to update
| Code: |
UPDATE ICS
SET ICS.CPT = DCS.CPT
FROM DB2TEST.GLH BP
INNER JOIN DB2TEST.GIH ILB ON
ILB.BLC = BP.LBC
INNER JOIN DB2TEST.GLH DTL ON
DTL.PCN = ILB.LBCN
INNER JOIN DB2TEST.CM BCS ON
BCS.CN = BP.LBCN
INNER JOIN DB2TEST.CM ICS ON
ICS.CN = ILB.LBCN
INNER JOIN DB2TEST.CM DCS ON DCS.CN = DTL.LBCN
WHERE BP.OC = 'CV'
AND BP.CUI = 'BP'
AND BP.LBS = 'A'
AND ILB.CUI = 'IB'
AND ILB.LBS = 'A'
AND DTL.CUI = 'DC'
AND DTL.LBS = 'A'
AND NOT ICS.CPT = DCS.CPT
|
but db2 says SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD FROM. Could you please help me how to make this work???
Code'd |
|
| Back to top |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
yes, would be glad to help your.
suggest that you look at the proper syntax of the UPDATE statement.
(BTW the proper syntax can be found in an SQL Reference Manual)
a hint, you are going to have to code a WHERE clause that contains a subquery. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
If this is imbedded SQL,
I would suggest DECLAREing a CURSOR for the first SQL
and include in the SELECT List,
the necessary columns to create an unique SELECT
which would then be used in a simple singleton UPDATE of ICS
with a WHERE Clause using the host variables
containing the primary key and the value for the SET clause.
and saving the expense of the two sqls with the joins.
of course, you could learn to write the mass-update SQL properly
and then I imagine, you would not need the first SQL. |
|
| Back to top |
|
|
|
|
