Merging Multiple Queries

[jwwceo]

I have this query(below), which I am using to update a field in my database. I am doing this in phpMyAdmin. But I have to run it 17 times, once for each color, times and would like to merge all queries into one so in the future I can run it just once.

 

Here's an individual query:

 

UPDATE xcart_variants, xcart_class_options, xcart_variant_items

SET xcart_variants.color_id ='ffffff'
WHERE xcart_variants.variantid = xcart_variant_items.variantid
AND xcart_variant_items.optionid = xcart_class_options.optionid
AND xcart_class_options.option_name = 'Black'

 

and here's what I have for a multiple query:

 

UPDATE xcart_variants, xcart_class_options, xcart_variant_items

SET xcart_variants.color_id ='432c00'
WHERE xcart_variants.variantid = xcart_variant_items.variantid
AND xcart_variant_items.optionid = xcart_class_options.optionid
AND xcart_class_options.option_name = 'Brown'

SET xcart_variants.color_id ='020950'
WHERE xcart_variants.variantid = xcart_variant_items.variantid
AND xcart_variant_items.optionid = xcart_class_options.optionid
AND xcart_class_options.option_name = 'Navy'

SET xcart_variants.color_id ='000000'
WHERE xcart_variants.variantid = xcart_variant_items.variantid
AND xcart_variant_items.optionid = xcart_class_options.optionid
AND xcart_class_options.option_name = 'Black'

SET xcart_variants.color_id ='ffffff'
WHERE xcart_variants.variantid = xcart_variant_items.variantid
AND xcart_variant_items.optionid = xcart_class_options.optionid
AND xcart_class_options.option_name = 'White'

 

Any ideas why I am getting errors??

 

James

[fenway]

You can't do multiple queries... just build a hash in php and run it in a loop.  You could do this in mysql directly with a bunch of case statements, but what's thepoint?