From: David Johnston <polobo@yahoo.com>
To: 'Matthias Nagel' <matthias.h.nagel@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Reuse temporary calculation results in an SQL update query [SOLVDED]
Date: Sun, 30 Sep 2012 12:26:00 -0400
Message-ID: <005e01cd9f28$481c39d0$d854ad70$@yahoo.com> (raw)
In-Reply-To: <2031491.bojg3UQVmk@hek506>
References: <43516431.HfO3TYfNBy@hek506>
<k46vmq$p11$1@ger.gmane.org>
<301621609.148693.1348931598720.JavaMail.open-xchange@ox.ims-firmen.de>
<2031491.bojg3UQVmk@hek506>
>
> thank you. The "WITH" clause did the trick. I did not even know that such a
> thing exists. But as it turns out it makes the statement more readable and
> elegant but not faster.
>
> The reason for the latter is that both the CTE and the UPDATE statement
> have the same "FROM ... WHERE ..." part, because the tempory calculation
> needs some input values from the same table. Hence the table is looked up
> twice instead once.
This is unusual; the only WHERE clause you should require is some kind of key matching...
Like:
UPDATE tbl
SET ....
FROM (
WITH final_result AS (
SELECT pkid, ....
FROM tbl
WHERE ...
) -- /WITH
SELECT pkid, .... FROM final_result
) src -- /FROM
WHERE src.pkid = tbl.pkid
;
If you provide an actual query better help may be provided.
David J.
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: polobo@yahoo.com, matthias.h.nagel@gmail.com
Subject: Re: Reuse temporary calculation results in an SQL update query [SOLVDED]
In-Reply-To: <005e01cd9f28$481c39d0$d854ad70$@yahoo.com>
* 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