Search code examples
sqlpostgresqlsql-update

Why does my UPDATE using a CTE affect all rows?

I have a task table with 1000 rows in my Postgres 12 database. I need to get last 100 rows non-executed, set column executed to 1 and return that selected rows. If I do code below, it updates all 1000 rows. Like it does not see LIMIT. It seems that's why this query with 100k rows executed took seconds! What is my mistake?

WITH cte AS (
    SELECT id, executed
    FROM task
    WHERE executed=0
    ORDER BY id DESC
    LIMIT 100
)
UPDATE task t
SET executed=1
FROM cte
RETURNING t.id
Solution

  • You didn't mention the relation between the two parts of the query, in the WHERE condition for the UPDATE:

    WITH cte AS (
    SELECT id, executed
    FROM task
    WHERE executed=0
    ORDER BY id DESC
    LIMIT 100
)
UPDATE task
SET executed=1
FROM cte
WHERE cte.id = task.id -- This one
RETURNING t.id