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.
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