Re: SQL question, updating more rows than subquery returns
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 05 Aug 2009 20:52:45 +0200
Message-ID: <4a79d4e3$0$195$e4fe514c_at_news.xs4all.nl>
gs schreef:
> 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
Date: Wed, 05 Aug 2009 20:52:45 +0200
Message-ID: <4a79d4e3$0$195$e4fe514c_at_news.xs4all.nl>
gs schreef:
> 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
and table_b.col_1 = 2?
Shakespeare Received on Wed Aug 05 2009 - 13:52:45 CDT