Message-ID: <4A702C9F.2020306@free.fr>
Date: Wed, 29 Jul 2009 13:03:59 +0200
From: nha <lyondif02@free.fr>
Organization: Lyon, Rhone, France
User-Agent: Thunderbird 2.0.0.6 (Macintosh/20070728)
MIME-Version: 1.0
To: =?ISO-8859-1?Q?=22Gau=2C_Hans-J=FCrgen=22?=
	<Hans-Juergen.Gau@LGN.Niedersachsen.de>
CC: PgSQL-sql <pgsql-sql@postgresql.org>
Subject: Re: WG: sql-porting-problem oracle to postgresql with UPDATE/IS
	NOT NULL
References: 
 <670F563845D6B34189933C9C7322B4872C089C@LV-MX-00002-V02.LV.ads.niedersachsen.de>
	<4A7022C4.9040802@free.fr>
In-Reply-To: <4A7022C4.9040802@free.fr>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit

Hello again,

Le 29/07/09 12:21, nha a écrit :
> Hello,
> 
> Le 29/07/09 11:48, Gau, Hans-Jürgen a écrit :
>> Sorry, it's a lapse by copying and simplification the original version. that
>> is correct:
>>
>> UPDATE table1 t1 
>> 	SET (t1.id) = 
>> 		(SELECT t3.id FROM table2 t2,table3 t3, table1 t1 
>> 		WHERE t3.field = t2.field 
>> 			AND t2.id = t1.id 
>> 			AND t1.id <> t3.id) 
>> 	WHERE 
>> 		(SELECT t3.id FROM table2 t2,table3 t3, table1 t1 
>> 		WHERE t3.field = t2.field 
>> 			AND t2.id = t1.id 
>> 			AND t1.id <> t3.id) IS NOT NULL; 
>> [...]
>> the solution brought by Daryl Richter has no effect.
> 
> There is one mistake [...]

To be concrete, a valid version would be:
UPDATE table1 t1
 SET id = (something)
 WHERE (anotherthing);

> Except this point, the query is valid although I am not sure the result
> is always what you may expect [...]

To "join" table1 column (assumed: id) to the subquery (sub-select here)
expression, table1 recall is not relevant in the subquery. Moreover an
alias "t1" is already defined to table1 (at the beginning of the statement).

A more suitable version would be:
UPDATE table1 t1
 SET id = (SELECT t3.id FROM table2 t2, table3 t3
  WHERE t3.field = t2.field AND t2.id = t1.id AND t1.id <> t3.id)
 WHERE (SELECT t3.id FROM table2 t2, table3 t3
  WHERE t3.field = t2.field AND t2.id = t1.id AND t1.id <> t3.id) IS NOT
NULL;

A PostgreSQL-compliant version could also be:
UPDATE table1 t1
 SET id = t3.id
 FROM table2 t2, table3 t3
 WHERE t3.field = t2.field AND t2.id = t1.id AND t1.id <> t3.id AND
t3.id IS NOT NULL;

A PostgreSQL-compliant and faster version may be:
UPDATE table1 t1
 SET id = t3.id
 FROM table2 t2
  INNER JOIN
   (SELECT t.id, t.field FROM table3 t WHERE t.id IS NOT NULL) t3
  ON t3.field = t2.field
  WHERE t2.id = t1.id AND t3.id <> t1.id;

Hoping a satisfying solution is up.

--
nha / Lyon / France.