IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

update SQL with sub-query


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
tzeche

New User


Joined: 08 Jun 2007
Posts: 3
Location: Philippines

PostPosted: Wed Mar 04, 2015 1:30 pm
Reply with quote

I have an update query below and need to convert it to test first whether the sub-query (SELECT MAX(COL_A) FROM TABLE2 WHERE COL_B = TB1.COL_2) returns a NULL value

UPDATE TABLE1 TB1
SET COL_1 = (SELECT MAX(COL_A) FROM TABLE2
WHERE COL_B = TB1.COL_2)


If the sub-query returns a NULL then COL_1 will retain it's existing value but if not then COL_1 will be updated with the value returned from the sub-query

Please help me, I can't seem to get it right.

Do I need to use CASE? How?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Mar 04, 2015 5:14 pm
Reply with quote

untested:
Quote:
UPDATE TABLE1 TB1
SET COL_1 = (SELECT MAX(COL_A) FROM TABLE2
WHERE COL_B = TB1.COL_2)
where exists(SELECT COL_A FROM TABLE2
WHERE COL_B = TB1.COL_2 and col_A is not null)
Back to top
View user's profile Send private message
tzeche

New User


Joined: 08 Jun 2007
Posts: 3
Location: Philippines

PostPosted: Thu Mar 05, 2015 11:04 pm
Reply with quote

Thank you GuyC! it worked fine!
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Help in writting SQL query DB2 1
No new posts SKIP LOCKED DATA in UPDATE statement DB2 9
No new posts Need Help with on of the coalesce query DB2 4
No new posts Group comparison/update between two f... DFSORT/ICETOOL 10
No new posts Mainframe ISPREDIT Macro query TSO/ISPF 3
Search our Forums:

Back to Top