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: 1280
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: 1280
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 FTP from Mainframe to Unix - with CRLF vasanthz All Other Mainframe Topics 7 Thu Mar 23, 2017 5:12 am
No new posts Want to pull the job log into a Mainf... Jishu Mukherjee Compuware & Other Tools 9 Tue Mar 21, 2017 4:48 pm
No new posts Innovations in Your Mainframe Sort Arun Raj PC Guides & IT News 4 Wed Mar 15, 2017 10:38 pm
No new posts IMS DB-How to update a record (a sing... Nic Clouston IMS DB/DC 9 Thu Mar 09, 2017 4:38 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am


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