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

combine two updates based on different condition into a single statement – postgresql

I want to combine two updates based on different condition into a single statement but getting an error and don’t understand any other way.

UPDATE "Main"
SET status = true
WHERE uid IN (
  SELECT uid
  FROM XYZ
)

UPDATE "Main"
SET status = false
WHERE uid NOT IN (
  SELECT uid
  FROM XYZ
)

I tried

UPDATE "Main"
SET status = CASE
  WHEN uid IN (
    SELECT uid
    FROM XYZ) THEN true
  ELSE false
END;

But getting error as
Unsafe query: ‘Update’ statement without ‘where’ updates all table rows at once

Tags:

2

Answers


  1. 0

    You can use the LEFT JOIN and CASE clauses; the records that have a false status are those that are not matched :

    UPDATE Main
SET status = CASE WHEN x.uid is not null then TRUE else FALSE end
FROM Main AS m
LEFT JOIN XYZ x ON x.uid = m.uid
WHERE Main.uid = m.uid; --this line is critical for updating ONLY joined rows
    Login or Signup to reply.
  2. 0

    You can use a common table expression (CTE) to determine the values to set based on the condition and then use a single UPDATE statement with a JOIN.

    WITH UpdateValues AS (
  SELECT uid, CASE WHEN uid IN (SELECT uid FROM XYZ) THEN true ELSE false END AS new_status
  FROM "Main"
)
UPDATE "Main" m
SET status = uv.new_status
FROM UpdateValues uv
WHERE m.uid = uv.uid;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search