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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
