pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
R: [SQL] UPDATE and SELECT result difference
2+ messages / 2 participants
[nested] [flat]

* R: [SQL] UPDATE and SELECT result difference
@ 2002-03-15 14:28 Enrico Mangano <enrico.mangano@nethouse.it>
  2002-03-15 16:19 ` Re: R: [SQL] UPDATE and SELECT result difference Jean-Luc Lachance <jllachan@nsd.ca>
  0 siblings, 1 reply; 2+ messages in thread

From: Enrico Mangano @ 2002-03-15 14:28 UTC (permalink / raw)
  To: Jean-Luc Lachance <jllachan@nsd.ca>; +Cc: pgsql-general@postgresql.org; 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@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:
> 
> 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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: R: [SQL] UPDATE and SELECT result difference
  2002-03-15 14:28 R: [SQL] UPDATE and SELECT result difference Enrico Mangano <enrico.mangano@nethouse.it>
@ 2002-03-15 16:19 ` Jean-Luc Lachance <jllachan@nsd.ca>
  0 siblings, 0 replies; 2+ messages in thread

From: Jean-Luc Lachance @ 2002-03-15 16:19 UTC (permalink / raw)
  To: enr1@libero.it, Andrea Giardina <andrea.giardina@nethouse.it>; pgsql-general@postgresql.org; +Cc: pgsql-sql

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.



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2002-03-15 16:19 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-15 14:28 R: [SQL] UPDATE and SELECT result difference Enrico Mangano <enrico.mangano@nethouse.it>
2002-03-15 16:19 ` Jean-Luc Lachance <jllachan@nsd.ca>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox