From: | "David Johnston" <polobo(at)yahoo(dot)com> |
---|---|
To: | "'Matthias Nagel'" <matthias(dot)h(dot)nagel(at)gmail(dot)com>, <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Reuse temporary calculation results in an SQL update query [SOLVDED] |
Date: | 2012-09-30 16:26:00 |
Message-ID: | 005e01cd9f28$481c39d0$d854ad70$@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
>
> 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Buck | 2012-10-02 00:47:02 | [noob] How to optimize this double pivot query? |
Previous Message | Johnny Winn | 2012-09-29 19:11:33 | Re: Need help with a special JOIN |