Postgres UPDATE using CASE with RETURNING returns all rows

Question

I execute the following query in postgres:

 UPDATE products SET dealer_id = 
                        CASE
                             WHEN order_id = 7 THEN '1' 
                             WHEN order_id = 6 THEN '2' 
                             ELSE dealer_id
                        END 
                        RETURNING id 

I expect to get ids of the updated rows like [3,4,5] but it returns all rows ids

what's wrong with my query?

Answer 1

You have no where clause, so you are updating all rows.

I think you intend:

 UPDATE products
    SET dealer_id = (CASE WHEN order_id = 7 THEN '1' 
                          WHEN order_id = 6 THEN '2' 
                          ELSE dealer_id
                     END)
    WHERE order_id IN (6, 7)
    RETURNING id ;

You may be interested in this explanation of why all rows are affected when you don't include a WHERE clause.

Answer 2

You might be missing where condition (filtering criteria). The current query will match all rows and tries to affect them based on case expression.

Answer 3

You're updating all the rows.

• When order_id is '7', you're setting dealer_id to 1.
• When order_id is '6', you're setting dealer_id to 2.
• For all other values of order_id, you're setting dealer_id to dealer_id. (That seems an odd thing to do.)

I hesitate to make any recommendations without more information, but this might be closer to what you want.

UPDATE products 
SET dealer_id = CASE
                    WHEN order_id = 7 THEN '1' 
                    WHEN order_id = 6 THEN '2' 
                END 
WHERE order_id in (7, 6)
RETURNING id