Results 1 to 2 of 2
Thread: Cross table update case query
Feb 16, 2013, 16:26 #1
- Join Date
- Oct 2009
- London, UK
- 1 Post(s)
- 0 Thread(s)
Cross table update case query
I have an images table containing `id`, `title` and `filename`. The filenames are randomly generated by php on upload for security.
I have a `catalogue` table containing `id`, `product_name`, `description` and `image`. The `image` field contains the `id` of the image associated with that product.
I want the user to enter the title of the image and have MySQL replace that with the relevant image id.
Now here's the kicker: I want to do it using a CASE clause.
This is what I've got so far:
UPDATE `catalogue` INNER JOIN `images` ON `images`.`id` = `catalogue`.`image` SET `product_name` = (CASE `catalogue`.`id` WHEN 123 THEN 'abc' WHEN 124 THEN 'abd' ELSE `catalogue`.`id`), `description` = (CASE `catalogue`.`id` WHEN 123 THEN 'Some useless crap' WHEN 124 'More stuff you don\'t need' ELSE `description` END) `image` = (CASE `images`.`title` WHEN 'image1' THEN `images`.`id` WHEN 'image2' THEN `images`.`id`);
As always, thanks in advance.