Update a column using another modified in the same query-CodePudding

I have a query to update two fields. The value of the first field is obtained with a subquery and the second is conditional, you can use the same subquery as field one or a different one. In the case of using the same subquery I simply assign the value of field one but it assigns its old value to me. Shouldn't I get the new value? How can I optimize this without repeating the subquery?

Example:

UPDATE table SET 
field1 = (SELECT count(*) FROM table2),
field2 (
    CASE WHEN condition THEN (SELECT count(*) FROM table2 WHERE field = 'foo') ELSE (SELECT count(*) FROM table2) END
);

My wrong solution:

UPDATE table SET 
field1 = (SELECT count(*) FROM table2),
field2 (
    CASE WHEN condition THEN (-- SUBQUERY2) ELSE field1 END
);

CodePudding user response：

To reuse the same subquery multiple times, use a Common Table Expression.

WITH memo AS (SELECT count(*) count FROM table2)
UPDATE table SET
field1 = (SELECT count FROM memo),
field2 = CASE WHEN condition THEN (...) ELSE (SELECT count FROM memo) END

As far as I know there's no good way to directly reference the new value for field1 when setting field2.