CODEWITHSUNDEEP
sqlpostgresqlcase
Anton Griduhsko
Anton Griduhsko

Reputation: 101

How to update several elements by id?

I try to update several cells, but nothing, could you help me to optimize my query?

with upd AS (
UPDATE era 
SET gender = CASE id
    WHEN 3 THEN 'Female'
    WHEN 4 THEN 'Male'
END,
SET city = CASE id
    WHEN 3 THEN 'Minsk'
    WHEN 4 THEN 'Brest'
END
WHERE id IN (3, 4)
returning *
)
select * from upd;

Upvotes: 1

Views: 65

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

I would suggest that you use a derived table for this logic:

WITH upd AS (
      UPDATE era 
          SET gender = v.gender,
              city = v.city
          FROM (VALUES (3, 'Female', 'Minsk'), (4, 'Male', 'Brest')
               ) v(id, gender, city)
          WHERE v.id = era.id
          RETURNING *
    )
SELECT * 
FROM upd;

Using this method, you don't have to repeat values or use complicated CASE expressions -- both of which are prone to error.

Upvotes: 3

Oto Shavadze
Oto Shavadze

Reputation: 42813

You just not need second SET

with upd AS (
    UPDATE era 
    SET gender = CASE id
        WHEN 3 THEN 'Female'
        WHEN 4 THEN 'Male'
    END,
    city = CASE id
        WHEN 3 THEN 'Minsk'
        WHEN 4 THEN 'Brest'
    END
    WHERE id IN (3, 4)
    returning *
)
select * from upd;

Upvotes: 1

Related Questions