Re: Select in update

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