CODEWITHSUNDEEP
mysqlsqlsql-update
leepowers
leepowers

Reputation: 38298

MySQL conditional UPDATE using IN

I have a database table with a column named featured which is used to highlight featured content on a page. Currently, I'm using two SQL statements to toggle the featured rows on/off like so:

-- Reset everything to non-featured
UPDATE tbl SET featured = '0'; 

-- Now set the selected rows as featured.
UPDATE tbl SET featured = '1' WHERE id IN (4, 10, 21);

Is it possible to combine both of these into one statement?

Upvotes: 1

Views: 2460

Answers (3)

Michael Buen
Michael Buen

Reputation: 39393

Use:

UPDATE tbl SET featured = id IN (4, 10, 21);

[EDIT]

@OMG Ponies:

that works, you can take advantage of the fact that boolean conditions in Mysql can be directly mapped to integer.

in Postgresql, you have to do this, need to cast the boolean to integer:

update tbl set featured = (id in ('b','d'))::int

Upvotes: 4

JHK
JHK

Reputation: 67

Yes, you can combine both of these into one statement.

Upvotes: -3

OMG Ponies
OMG Ponies

Reputation: 332531

Use:

UPDATE tbl
   SET featured = CASE 
                    WHEN id IN (4, 10, 21) THEN '1'
                    ELSE '0'
                  END;

Upvotes: 7

Related Questions