Re: R: [SQL] UPDATE and SELECT result difference

Hello again Enrico,

Your first UPDATE will update 1 record 3 times; there is only one record
updated.
The second UPDATE is not equivalant and will return an error as the
sub-select should not return more than one tuple.

But, you can write:

UPDATE tab_test1
SET campo1 = (
  SELECT campo2
  FROM tab_test2
  WHERE tab_test1.cod = tab_test2.cod
  ORDER BY campo2
  LIMIT 1);

jll

Enrico Mangano wrote:
>
> Thank you, Jean-Luc!
>
> But now I would have another question about this:
>
> cod  | campo1
> -----+---------
>  1   | valore1
> (1 row)
>
> cod  | campo2
> -----+---------
>  1   | valore2
>  1   | valore3
>  1   | valore4
>
> targhettariodb=# update tab_test1 set campo1 = tab_test2.campo2 where
> tab_test1.cod = tab_test2.cod;
> UPDATE 1
> targhettariodb=# select * from tab_test1;
>  cod | campo1
> -----+---------
>  1   | valore3
> (1 row)
>
> I think the value it has chosen is random, isn't it?
> So I can't have any control on it.
>
> The UPDATE query above is semantically equivalent to this:
> update tab_test1 set campo1 = (select campo2 from tab_test2, tab_test1
> where tab_test1.cod = tab_test2.cod);
> And this query(IMHO in a correct way) return an ERROR:
> targhettariodb=# update tab_test1 set campo1 = (select campo2 from
> tab_test2, tab_test1 where tab_test1.cod = tab_test2.cod);
> ERROR: More than one tuple returned by a subselect used as an
> expression.
>
> Isn't this an incongruence in SQL?
>
> Thanks,
> Enrico.