DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:From:To:References:In-Reply-To:Subject:Date:Message-ID:MIME-Version:Content-Type:Content-Transfer-Encoding:X-Mailer:Thread-Index:Content-Language;
	b=Ask/BjjkYCHKJQ3PeWAr5AxuN7fCdqJHSk8YoDQofBTxKQ+9umRUr6lZjsWb/TjS5uYhm/wWyVPgfWFhaImu3NmEUtGJNs87hu69R6jXsf5LAcdL51mbrvoD/1RCkuKK5ouUGPSXw52UJ+AoITVq3Yiw44F7pT7sBKwiKC+ZRPs=
	;
From: "David Johnston" <polobo@yahoo.com>
To: "'Matthias Nagel'" <matthias.h.nagel@gmail.com>,
 <pgsql-sql@postgresql.org>
References: <43516431.HfO3TYfNBy@hek506> <k46vmq$p11$1@ger.gmane.org>
	<301621609.148693.1348931598720.JavaMail.open-xchange@ox.ims-firmen.de>
	<2031491.bojg3UQVmk@hek506>
In-Reply-To: <2031491.bojg3UQVmk@hek506>
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>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Thread-Index: AQHv9xOxWZP3/uYjb4rfEQresSPOSAHdk8KYAY3t8MEB0UZRepc0e1JA
Content-Language: en-us

>=20
> 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.
>=20
> 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 =3D tbl.pkid
;

If you provide an actual query better help may be provided.

David J.