pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jean-Luc Lachance <jllachan@nsd.ca>
To: enr1@libero.it, Andrea Giardina <andrea.giardina@nethouse.it>
To: pgsql-general@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: R: [SQL] UPDATE and SELECT result difference
Date: Fri, 15 Mar 2002 11:19:47 -0500
Message-ID: <3C921F23.C7477776@nsd.ca> (raw)
References: <876C89678A051145876D939BF4B7A82C235AE6@net1.hq.nethouse.it>

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.



view thread (2+ messages)

Message-ID: <3C921F23.C7477776@nsd.ca>
Permalink:  ../3C921F23.C7477776@nsd.ca/
Also on:    postgresql.org/message-id/3C921F23.C7477776@nsd.ca

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: jllachan@nsd.ca, andrea.giardina@nethouse.it, pgsql-general@postgresql.org
  Subject: Re: R: [SQL] UPDATE and SELECT result difference
  In-Reply-To: <3C921F23.C7477776@nsd.ca>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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