Updating column value to the ID associated with it in another table.

Posted on 2011-10-17
Last Modified: 2012-05-12
Ok working on someone else's code I've run into the following problem with their photo gallery. There is a table to store the image, image path, gallery, etc... and a table to store the gallery name, a short name (for the url) and an id for the gallery. However the image table is story the short name for the gallery instead of the id. Example data:

|id_image|img_filename|img_ext|img_path|  img_gal  | img_date |img_photographer|
|   1    | dsc_004    | jpg   | ...    | golf_2011 |2011-06-10| My Name        |
|   2    | dsc_027    | jpg   | ...    | golf_2011 |2011-06-10| My Name        |
|   3    | dsc_523    | jpg   | ...    | dtco_2011 |2011-09-20| Someone's Name |
|   4    | dsc_544    | jpg   | ...    | dtco_2011 |2011-09-20| My Name        |

Open in new window

|id_gal|  gal_name         | gal_short |
|   1  | 2011 Golf Outting | golf_2011 |
|   2  | 2011 Celebrity ...| dtco_2011 |

Open in new window

What I'm trying to do is create a query, that will update the gallery_images table, img_gal column to instead have the id of the gallery as listen in the gallery_groups table.
Question by:Derokorian
    LVL 10

    Accepted Solution

    Nevermind - I found out you can subquery within an update query. All my data is the way I want it now, just need to fix some code that relied on poor design. My query looks like:
    UPDATE gallery_images SET
       img_gal = (SELECT id_gal FROM gallery_groups WHERE gal_short = img_gal)

    Open in new window

    LVL 10

    Author Closing Comment

    I was able to find the answer on my own. Sorry to waste the experts' time!
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    though the question is closed, you can read up this article for update with join:

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi ( had suggested a “sed” way, I actually shell …
    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now