Re: Reuse temporary calculation results in an SQL update query [SOLVDED]

>
> 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.