MySQL: flag = 1 when id IN(...) otherwise 0 ?

  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13503
  • Loc: Florida

Post 3+ Months Ago

I've got a categories table and there is an indexed flag column that I check to see if the category should be displayed on the index.

I've pretty much got all of my per-unit editors setup and now I'm working on bulk editors. My current design for the bulk flag editor has me creating <ul> hierarchies with checkboxes in the <li> elements for each category.

Here's an example of my checkbox. I plan on having a simple array of category id integers corresponding to categories that should have the flag set.

HTML Code: [ Select ]
<li><label><checkbox name="flags[{category_id}]" .../>


Now the two-query way to do this is pretty straight forward.

SQL Code: [ Select ]
UPDATE categories SET flag = FALSE

SQL Code: [ Select ]
UPDATE categories SET flag = TRUE WHERE id IN (...)


I don't want to use two queries though. Having 1000 rows in this table would be an extremely gross over-estimate of the max anticipated rows.

How can I set the flag to both needed states with a single query in MySQL having an array of category ids that should have the flag set TRUE as input ?
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13503
  • Loc: Florida

Post 3+ Months Ago

By jove I think I've got it !

SQL Code: [ Select ]
UPDATE categories
SET flag =
CASE category_id IN (1,3,5,7,9) WHEN TRUE THEN TRUE
ELSE FALSE END
  1. UPDATE categories
  2. SET flag =
  3. CASE category_id IN (1,3,5,7,9) WHEN TRUE THEN TRUE
  4. ELSE FALSE END
  • Bigwebmaster
  • Site Admin
  • Site Admin
  • User avatar
  • Posts: 9090
  • Loc: Seattle, WA & Phoenix, AZ

Post 3+ Months Ago

Yup, I was just about to mention the CASE.. WHEN.. THEN.. ELSE.. END syntax to you. I recently needed something like this too for something I was working on.
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13503
  • Loc: Florida

Post 3+ Months Ago

I'd already thought about CASE when I'd started the thread, but I was having problems getting the IN to work for me.

I kept wanting to do something like this instead of the way I have it up there.

SQL Code: [ Select ]
UPDATE categories
SET flag =
CASE category_id WHEN IN (1,3,5,7,9) THEN TRUE
ELSE FALSE END
  1. UPDATE categories
  2. SET flag =
  3. CASE category_id WHEN IN (1,3,5,7,9) THEN TRUE
  4. ELSE FALSE END


This would give me errors like this, which was making me wonder if CASE could support IN at all.

Code: [ Select ]
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IN (1,3,5,7,9) THEN TRUE ELSE FALSE END' at line 3

Post Information

  • Total Posts in this topic: 4 posts
  • Users browsing this forum: No registered users and 57 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.