Using results from CTE giving ERROR: missing FROM-clause entry for table "cte"-CodePudding

I'm trying to use the results from my first query to perform a second query

WITH cte AS(
    SELECT * FROM test_user WHERE user_id = NEW.user_id
)
UPDATE test_username SET money_counter = money_counter   1, total_revenue = total_revenue   cte.total_revenue
WHERE username = cte.username;

But I'm getting an error

ERROR:  missing FROM-clause entry for table "cte"
LINE 5:  WHERE username = cte.username
                          ^
QUERY:  WITH cte AS(
        SELECT * FROM test_user WHERE user_id = NEW.user_id
    )
    UPDATE test_username SET money_counter = money_counter   1, total_revenue = total_revenue   cte.total_revenue
    WHERE username = cte.username
CONTEXT:  PL/pgSQL function test_trigger() line 4 at SQL statement
SQL state: 42P01

Not sure what I'm doing incorrectly.

This is from the returned results from the query:

SELECT * FROM test_user WHERE user_id = 1;

And the table I'm performing the second query on:

CodePudding user response：

The WITH clause just defines a common table expression. You still need a FROM clause to access it. Using your query 'as is' -

WITH cte AS (
    SELECT * FROM test_user WHERE user_id = NEW.user_id
)
UPDATE test_username
SET money_counter = money_counter   1, 
    total_revenue = total_revenue   cte.total_revenue
FROM cte
WHERE test_username.username = cte.username;