From: nha <lyondif02@free.fr>
To: "Gau, Hans-Jürgen" <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
Date: Wed, 29 Jul 2009 12:21:56 +0200
Message-ID: <4A7022C4.9040802@free.fr> (raw)
In-Reply-To: <670F563845D6B34189933C9C7322B4872C089C@LV-MX-00002-V02.LV.ads.niedersachsen.de>
References: <670F563845D6B34189933C9C7322B4872C089C@LV-MX-00002-V02.LV.ads.niedersachsen.de>
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.
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: lyondif02@free.fr, Hans-Juergen.Gau@LGN.Niedersachsen.de
Subject: Re: WG: sql-porting-problem oracle to postgresql with UPDATE/IS NOT NULL
In-Reply-To: <4A7022C4.9040802@free.fr>
* 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