Обсуждение: Select in update

Hello!

I need to update a table (13000 rows) using something
like this:
update rua  set cod_bairro =
(select cod_bairro from bairro b where b.vetbairro =
r.vetbairro)

Is it possible?? Can I use a select statement as input
for an update??

Thanks,
JP


__________________________________________________
Do You Yahoo!?
Sign up for SBC Yahoo! Dial - First Month Free
http://sbc.yahoo.com

On Mon, 8 Jul 2002 12:35:23 -0700 (PDT), "João" Paulo Batistella
<batistellabr@yahoo.com.br> wrote:
>I need to update a table (13000 rows) using something
>like this:
>update rua  set cod_bairro =
>(select cod_bairro from bairro b where b.vetbairro =
>r.vetbairro)

JP, you were close to it.  Your statement would work if you change it
to
update rua
   set cod_bairro = (select cod_bairro
                       from bairro b
                      where b.vetbairro = rua.vetbairro)
                                          ^^^
and if vetbairro is unique in bairro (at least for the values of
vetbairro appearing in rua).  And if it works, then the following
statement should also work, probably faster:

UPDATE rua
   SET cod_bairro = b.cod_bairro
  FROM bairro b
 WHERE b.vetbairro = rua.vetbairro;

Servus
 Manfred

> Is it possible?? Can I use a select statement as input
> for an update??

With 7.2.1, no problem. But you have to think about what happens
if the select returns more then one tuple (errr row :-) as this
will result in an error.