Thread: UPDATE and SELECT result difference

UPDATE and SELECT result difference

From

"Enrico Mangano"

Date:

14 March 2002, 15:20:38

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
 (1 row)
)

>psql (PostgreSQL) 7.1.3
>contains readline, history support
>Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
>Portions Copyright (c) 1996 Regents of the University of California
>Read the file COPYRIGHT or use the command \copyright to see the
>usage and distribution terms.

Thanks all.

Regards.

Enrico Mangano
Net Software
________

NETHOUSE S.R.L.
C.so Re Umberto I, 57 - 10128 Torino - Italy
Tel. +39-011-581.581 - Fax +39-011-581.591
http://www.nethouse.it <http://www.nethouse.it/>    -
enrico.mangano@nethouse.it <mailto:enrico.mangano@nethouse.it>

Re: UPDATE and SELECT result difference

From

Jean-Luc Lachance

Date:

14 March 2002, 16:45:16

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