SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Hybrid View

  1. #1
    SitePoint Addict
    Join Date
    Oct 2009
    Location
    London, UK
    Posts
    382
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question Cross table update case query

    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:
    Code:
    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. #2
    SitePoint Addict
    Join Date
    Oct 2009
    Location
    London, UK
    Posts
    382
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    OK, so I managed to work it out. So if anyone's interested, here's how you do it:

    Code:
    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...



Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •