CODEWITHSUNDEEP
sqlpostgresql
Matt
Matt

Reputation: 22911

Postgres: UPDATE a boolean column that sets all other booleans in that column to false

I'm wondering if I can do this in one query

Usecase: twitter pinned tweet. You can have at most one pinned tweet and setting a new pinned tweet, unset all the other previously pinned tweets.

Any ideas?

Upvotes: 16

Views: 31658

Answers (1)

Jakub Kania
Jakub Kania

Reputation: 16487

UPDATE tweets
SET pinned = NOT pinned
WHERE id = 1234 OR pinned = TRUE;

Or to be extra cautious

WHERE (id = 1234 AND pinned = FALSE) OR (pinned = TRUE AND id <> 1234)

Upvotes: 21

Related Questions