SQL question, updating more rows than subquery returns

From: gs <gs_at_gs.com>
Date: Wed, 05 Aug 2009 18:45:42 GMT
Message-ID: <qrkem.39683$PH1.16096_at_edtnps82>

A little rusty at my SQL here, perhaps someone could point out where I'm going wrong. I need to update all the records in table b where they meet a condition in table a, a query shows all the records that need updating:

select b.col_1 from table_a a, table_b b where a.col_2 = b.col_2
and a.col_1=1
and b.col_1=2;

This query returns 5383 rows

Now I need to update all the values in b.col1 from 2 to 1 where the corresponding value in table a (a.col1) has a 1, so I build this update statement:

update table_b
set col_1=1
where col_1 in
(select b.col_1 from table_a a, table_b b where a.col_2 = b.col_2
and a.col_1=1
and b.col_1=2);

This should work, correct? But I get 5790 rows updated. What am I missing here?

tia Received on Wed Aug 05 2009 - 13:45:42 CDT