Re: SQL question, updating more rows than subquery returns

   set b.col_1 = 1
   where exists ( select 1

                           from    table_a a
                           where  a.col_2 = b.col_2
                               and  a.col_1 = 1
                               and  b.col_1 = 2
                       )

"gs" <gs_at_gs.com> escribió en el mensaje news: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 - 15:44:57 CDT