From: | "Enrico Mangano" <enrico(dot)mangano(at)nethouse(dot)it> |
---|---|
To: | "Jean-Luc Lachance" <jllachan(at)nsd(dot)ca> |
Cc: | <pgsql-general(at)postgresql(dot)org>, <pgsql-sql(at)postgresql(dot)org> |
Subject: | R: [SQL] UPDATE and SELECT result difference |
Date: | 2002-03-15 14:28:02 |
Message-ID: | 876C89678A051145876D939BF4B7A82C235AE6@net1.hq.nethouse.it |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-sql |
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(at)nsd(dot)ca]
Inviato: giovedì 14 marzo 2002 19.47
A: Enrico Mangano
Cc: pgsql-general(at)postgresql(dot)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:
>
> Hi all,
>
> of course I'm too drunk, or simply I'm missing something, but
> I can't understand the difference beetwen the results of these
> 2 queries:
>
> targhettariodb=# UPDATE temp_principale
> targhettariodb-# SET cda_istat = acxx_aziende_istat.cda_istat ,
> targhettariodb-# prg_istat = acxx_aziende_istat.prg_istat
> targhettariodb-# WHERE acxx_aziende_istat.cda_azienda =
> temp_principale.cda_azienda;
> UPDATE 1727
> targhettariodb=# SELECT count(*)
> targhettariodb-# FROM acxx_aziende_istat,temp_principale
> targhettariodb-# WHERE acxx_aziende_istat.cda_azienda =
> temp_principale.cda_azienda;
> count
> -------
> 1929
> (1 row)
>
> why this difference in the number of rows?
>
> (
> targhettariodb=# SELECT count(*)
> targhettariodb-# FROM acxx_aziende_istat, temp_principale
> targhettariodb-# WHERE
> targhettariodb-# acxx_aziende_istat.cda_azienda is null OR
> targhettariodb-# temp_principale.cda_azienda is null OR
> targhettariodb-# acxx_aziende_istat.cda_istat IS NULL OR
> targhettariodb-# acxx_aziende_istat.prg_istat IS NULL ;
> count
> -------
> 0
From | Date | Subject | |
---|---|---|---|
Next Message | Jan Wieck | 2002-03-15 14:47:23 | Re: more about pg_toast growth |
Previous Message | Christian von Kietzell | 2002-03-15 14:24:10 | How to get the name of a table's primary key? |
From | Date | Subject | |
---|---|---|---|
Next Message | Laurent N | 2002-03-15 15:41:52 | few admin questions |
Previous Message | Llew Sion Goodstadt | 2002-03-15 12:46:17 | Re: where not exists |