R: [SQL] UPDATE and SELECT result difference

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.

-----Messaggio originale-----
Da: Jean-Luc Lachance [mailto:jllachan@nsd.ca]
Inviato: giovedì 14 marzo 2002 19.47
A: Enrico Mangano
Cc: pgsql-general@postgresql.org
Oggetto: Re: [GENERAL] UPDATE and SELECT result difference

Hello Enrico,

It simply means that there are 202 duplicate rows in acxx_aziende_istat
where acxx_aziende_istat.cda_azienda = temp_principale.cda_azienda.

try :

SELECT cda_azienda, COUNT(*)
FROM acxx_aziende_istat
WHERE EXISTS (
SELECT * FROM temp_principale
WHERE acxx_aziende_istat.cda_azienda = temp_principale.cda_azienda)
GROUP BY cda_azienda
HAVING COUNT(*) > 1;

jll

Enrico Mangano wrote: