MySQL conditional UPDATE using IN

Question

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?

OMG Ponies · Accepted Answer · 2010-01-20 05:35:46Z

7

Use:

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

answered Jan 20, 2010 at 5:35

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Michael Buen · Accepted Answer · 2010-01-20 14:47:05Z

4

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

edited Jan 20, 2010 at 14:47

answered Jan 20, 2010 at 6:14

Michael Buen

39.6k10 gold badges99 silver badges120 bronze badges

2 Comments

OMG Ponies Over a year ago

Cool. The OP has the numbers within single quotes, just have to cast/convert if it's necessary.

mechanical_meat Over a year ago

indeed, the auto-conversion would be a performance hit if you didn't explicitly cast/convert

JHK · Accepted Answer · 2010-01-20 05:35:50Z

-3

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

answered Jan 20, 2010 at 5:35

JHK

675 bronze badges

4 Comments

ddc0660 Over a year ago

While that may literally answer the question asked, I don't believe this is a helpful response. The implied question is "how can a single statement be constructed to do this?"

Bill Karwin Over a year ago

@jigs: Nobody likes a smart alec.

JHK Over a year ago

UPDATE Activity SET Activity.Charges = 10 WHERE (Activity.[Activity ID]) In (2,3,4); This is a example which is working.

Michael Buen Over a year ago

@ddc0660: maybe jigs have acquired bad habits from becoming a programmer heheh :D stackoverflow.com/questions/164432/…

Collectives™ on Stack Overflow

MySQL conditional UPDATE using IN

3 Answers 3

Comments

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related