Update using where exists

[pbouzaid]

Howdy,

I am trying to update a table using the where exists and I'm not getting the expected updates.

My SQL is as follows:

UPDATE FF.TABLE1
SET COL6 = 'Y',
COL7 = 'Y'
WHERE EXISTS
(SELECT *
FROM FF.TABLE1 A, FF.TABLE2 B
WHERE (B.COL9 = 101030
OR B.COL9 = 101031)
AND B.COL2 = A.COL2);

I expect COL6 and COL7 in the TABLE1 to update only when the where exists returns a match. If I run the where exists select on its own I get 5 rows returned which is what I expect. If I run this whole SQL then every row in TABLE1 is updated, instead of the 5 I want updated.

Any thoughts on how to fix this?

Regards,

Pete.


Edit:

Someone suggested I change the SQL to the following:

UPDATE FF.TABLE1
SET COL6 = 'Y',
COL7 = 'Y'
WHERE EXISTS (SELECT A.COL6, A.COL7
FROM FF.TABLE1 A, FF.TABLE2 B
WHERE (B.COL9 = 101030
OR B.COL9 = 101031)
AND B.COL2 = A.COL2);

but still every row in the table is being updated. It almost seems the SQL execution is stopping at where exists and the select is not being executed.....

[pbouzaid]

never mind guys. i worked it out using a different method.

all good now.

[Natarajan]

Hi Pete,

can you update us, what is that different method.

[pbouzaid]

I did it this way:

UPDATE FF.TABLE1 A
SET COL6 = 'Y',
COL7 = 'Y'
WHERE A.COL2 IN
(SELECT B.COL2 FROM FF.TABLE2 B
WHERE (B.COL9 = 000001
OR B.COL9 = 000002));

Seems to work quite well.

[Natarajan]

thanks.