Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

UPDATE with JOIN in DB2 Mainframe

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Achroo

New User


Joined: 07 Jan 2008
Posts: 7
Location: noida

PostPosted: Mon Feb 22, 2010 8:20 am    Post subject: UPDATE with JOIN in DB2 Mainframe
Reply with quote

Hi

Can we use update with join in any way.

I am currently using

Update Table_A Inner Join Table_B on Table_A(col1) = Table_B(col2)
Set Col_3 = some value Where some condition;

But I am getting SQLCode= -199 "Illegal use of word Inner" with SQLstate = 42601.

I have tried with only Inner and JOIN as well. but same error code is coming.

Is there any way out?

Thanks.
Achroo
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Feb 22, 2010 9:19 am    Post subject:
Reply with quote

Hello,

Quote:
Is there any way out?
Not the way you are coding the sql. . .

If you post the actual requirement, someone may have a suggestion.

You should be able to do what you want without an inner/outer join. Something like:
Quote:
When you need to update one table based on the other table -
it is called "correlated update". You have to repeat the same "where"
logic 2 times.
Code:

update maintab m 
set (m.fname, m.lname) = 
 (select u.fname,u.lname from updatetab u where m.id=u.id) 
where exists 
 (select null from updatetab u where m.id=u.id); 

or variation: use "in" expression:

update maintab m 
set (m.fname, m.lname) =
 (select u.fname,u.lname from updatetab u where m.id=u.id) 
where m.id in 
 (select u.id from updatetab); 

Back to top
View user's profile Send private message
Achroo

New User


Joined: 07 Jan 2008
Posts: 7
Location: noida

PostPosted: Mon Feb 22, 2010 11:31 am    Post subject:
Reply with quote

Hi

Basically I am running SQL query through SPUFI instead of program.

I am using JOIN instead of SUBQUERY beacuse it returns multiple rows.


Query1


SELECT B.COL1,B.COL2,C.COL2

FROM TABLE_1 B,TABLE_2 C

WHERE B.COL2 ¬= C.COL2
AND B.COL1 = C.COL1
AND C.COL3= '6'
AND C.COL4 = ' '
;


The above query is running correctly and returns n no. of rows.

Now I need to update value of COL2 of TABLE_1 with COL2 of TABLE_2.

Therefore I am using following Update query


UPDATE TABLE_2 C

INNER JOIN TABLE_1 B ON

B.COL2 ¬= C.COL2
AND B.COL1 = C.COL1

SET B.COL2 = C.COL2

WHERE C.COL3= '6'
AND C.COL4 = ' '

;


But I am getting SQLCode -199.

Thanks
Achroo
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Feb 22, 2010 1:08 pm    Post subject:
Reply with quote

Hello,

Yes, because what you are doing is invalid. . .
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Feb 22, 2010 3:03 pm    Post subject:
Reply with quote

Code:
UPDATE TABLE_1 B
SET B.COL2 =  (select  C.COL2  from  TABLE_2 C
                        where B.COL1 = C.COL1
                           and B.COL2 ¬= C.COL2
                           and C.COL3= '6'
                           AND C.COL4 = ' ' )
where exists(select  C.COL2  from  TABLE_2 C
                  where B.COL1 = C.COL1
                      and B.COL2 ¬= C.COL2
                      and C.COL3= '6'
                     AND C.COL4 = ' ' )

this should work as long as there is maximum only one row in table_2 for each COL1, '6', ' '
otherwise you'll have to add min() or max() and group by, or extra criteria on table2
Back to top
View user's profile Send private message
cdhami

New User


Joined: 24 Jan 2006
Posts: 28

PostPosted: Fri Mar 05, 2010 7:58 pm    Post subject:
Reply with quote

Hi Dick,
I have tried the query you mentioned..its not working for DB2 to update a table using data from another table.

UPDATE UU01.TLT_STD_LOROCOMB_S A SET
( A.SUBPAY_FLOW_CODE
,A.CHARGE_CODE_1
)
=
( SELECT B.SUBPAY_FLOW_CODE
,B.CHARGE_CODE_1
FROM UU01.TLT_STAN_LORO_COMB B
WHERE B.COMBINATION_ID = '001'
)
WHERE A.COMBINATION_ID ='001'


DSNT408I SQLCODE = -401, ERROR: THE OPERANDS OF AN ARITHMETIC OR COMPARISON
OPERATION ARE NOT COMPARABLE
DSNT418I SQLSTATE = 42818 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXOBFC SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 930 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000003A2' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION

Pls suggest
Thanks
Cdhami
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Mar 05, 2010 8:09 pm    Post subject:
Reply with quote

Is COMBINATION_ID character in both tables ?
Is CHARGE_CODE_1 of same type in both tables ?
Is SUBPAY_FLOW_CODE of same type in both tables ?
Back to top
View user's profile Send private message
cdhami

New User


Joined: 24 Jan 2006
Posts: 28

PostPosted: Fri Mar 05, 2010 8:16 pm    Post subject:
Reply with quote

hi
the query is working fine..

thx all
Back to top
View user's profile Send private message
cdhami

New User


Joined: 24 Jan 2006
Posts: 28

PostPosted: Fri Mar 05, 2010 8:17 pm    Post subject:
Reply with quote

the combination id was not having correct format ..its numeric..
thx
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Join in SQL Query vickey_dw DB2 1 Tue Jan 17, 2017 12:15 am
No new posts Produce Mainframe Dash Board Reports ... sdcarlson53 All Other Mainframe Topics 2 Thu Jan 05, 2017 11:01 pm
This topic is locked: you cannot edit posts or make replies. Urgent Mainframe Requirments (2-10 yr... Shankar779 Mainframe Jobs 0 Sun Nov 27, 2016 1:48 pm
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us