Received: from beamish.nsd.ca (unknown [205.150.156.194]) by postgresql.org (Postfix) with ESMTP id F1A06475F0B; Fri, 15 Mar 2002 11:17:01 -0500 (EST) Received: (from smap@localhost) by beamish.nsd.ca (8.9.3/8.9.3) id LAA22729; Fri, 15 Mar 2002 11:17:02 -0500 X-Authentication-Warning: beamish.nsd.ca: smap set sender to using -f Received: from reddog.nsd.ca(192.168.101.30) by beamish.nsd.ca via smap (V2.1/2.1+anti-relay+anti-spam) id xma022725; Fri, 15 Mar 02 11:16:45 -0500 Received: from nsd.ca (jllachan-linux.nsd.ca [192.168.101.148]) by reddog.nsd.ca (8.8.7/8.8.7) with ESMTP id LAA08262; Fri, 15 Mar 2002 11:20:18 -0500 Message-ID: <3C921F23.C7477776@nsd.ca> Date: Fri, 15 Mar 2002 11:19:47 -0500 From: Jean-Luc Lachance X-Mailer: Mozilla 4.77 [en] (X11; U; Linux 2.4.9-12 i686) X-Accept-Language: en MIME-Version: 1.0 To: enr1@libero.it, Andrea Giardina , pgsql-general@postgresql.org Cc: pgsql-sql@postgresql.org Subject: Re: R: [SQL] UPDATE and SELECT result difference References: <876C89678A051145876D939BF4B7A82C235AE6@net1.hq.nethouse.it> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200203/1006 X-Sequence-Number: 22375 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.