Update rows when in other table

[elziko]

I have two tables TAB_A and TAB_B. I want to update COL_1 in TAB_A to a
certain value but only for rows where the value COL_2 in TAB_A is also found
in COL_2 of TAB_B.

This is what I have but it seems to then set COL_1 of TAB_A to a NULL if its
not found in TAB_B. In this case I just want to leave that row unchanged.

UPDATE tab_a
SET col_1 = 'NEWVALUE'
WHERE EXISTS (SELECT 1
FROM tab_a a, tab_b b
WHERE a.col_2 = b.col_2)
AND col_1 IS NULL;

Any ideas to what I'm doing wrong?

--

Cheers,

elziko

[Laconic2]

Re: Update rows when in other table


"elziko" <elziko@NOTSPAM MINGyahoo.co.uk wrote in message
news:40b468a8$0 $7727$afc38c87@ news.easynet.co .uk...

I have two tables TAB_A and TAB_B. I want to update COL_1 in TAB_A to a
certain value but only for rows where the value COL_2 in TAB_A is also

found

in COL_2 of TAB_B.

Try this instead:

UPDATE tab_a a
SET col_1 = 'NEWVALUE'
WHERE EXISTS (SELECT 1
FROM tab_b b
WHERE a.col_2 = b.col_2)
AND col_1 IS NULL;


It only references tab_a once rather than twice.

[elziko]

Re: Update rows when in other table

It only references tab_a once rather than twice.

Excellent, thank you very much!

--

Cheers,

elziko