View previous topic :: View next topic
|
Author |
Message |
tzeche
New User
Joined: 08 Jun 2007 Posts: 3 Location: Philippines
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
tzeche
New User
Joined: 08 Jun 2007 Posts: 3 Location: Philippines
|
|
|
|
Thank you GuyC! it worked fine! |
|
Back to top |
|
|
|