Re: SQL question, updating more rows than subquery returns

> 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

You are updating all rows in table_b where col_1=2 since that is what the subquery gives. Is just gives a set of 2's which are distincted to just one row when used in an in-expression.
You could do something like that:

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

That should do the trick.

Hope that helps,
Lothar

-- 
Lothar Armbrüster  | lothar.armbruester_at_t-online.de
Hauptstr. 26       |
65346 Eltville     |