Re: WG: sql-porting-problem oracle to postgresql with UPDATE/IS NOT NULL

[nha <lyondif02(at)free(dot)fr>]

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; 
> 
> or in very simplified form:
> 
> UPDATE table t1
> 	SET (t1.id)=(SELECT expression)
> 	WHERE 	(SELECT expression) IS NOT NULL;
> 
> The SELECT expressions are identical.
> 
> this syntax is allowed on postgresql?
> 
> the solution brought by Daryl Richter has no effect.
> 		
> Regards, Hans
> [...]

There is one mistake syntactically speaking and according to PostgreSQL
specification for UPDATE statement: column specified in the SET part
cannot be qualified with an existing alias for the updated table. See also:
http://www.postgresql.org/docs/8.4/interactive/sql-update.html

Except this point, the query is valid although I am not sure the result
is always what you may expect because there is no relation between any
field of the updated table and the SELECT expression in the WHERE
clause; table1 is called (in fact, a copy of this table is implied) but
no column of this table is bound to one or more of the current updated
table1. I may mistake...

Regards.

--
nha / Lyon / France.