Question 246960 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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
Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    You must establish the link between target table and the CTE:

    WITH cte AS (
   SELECT id
   FROM   task
   WHERE  executed = 0
   ORDER  BY id DESC
   LIMIT  100
   )
UPDATE task t
SET    executed = 1
FROM   cte c
WHERE  c.id = t.id  -- !!!
RETURNING t.id;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search