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

Question

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?

Answer 1

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)