Cross table update case query

Databases
1

Hi there,

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`);

Obviously it doesn’t work… Before I lose any more of my life on this, I’m just wondering if a) its possible, and b) whether I’m on the right lines.

As always, thanks in advance.

M

2

OK, so I managed to work it out. So if anyone’s interested, here’s how you do it:


UPDATE `catalogue`
SET `product_name` = (CASE `id` WHEN 123 THEN 'abc' WHEN 124 THEN 'abd' ELSE `id`),
    `description` = (CASE `id` WHEN 123 THEN 'Some useless crap' WHEN 124 'More stuff you don\\'t need' ELSE `description` END)
    `image` = (CASE `id` WHEN 123 THEN (SELECT `id` FROM `images` WHERE `title` = 'image1') WHEN 124 THEN (SELECT `id` FROM `images` WHERE `title` = 'image2') ELSE `image` END);

Simple really…

:slight_smile: