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

Posted on 2011-10-17
Medium Priority
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
  • 2
LVL 10

Accepted Solution

Derokorian earned 0 total points
ID: 36980446
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

ID: 36980451
I was able to find the answer on my own. Sorry to waste the experts' time!
LVL 143

Expert Comment

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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month16 days, 20 hours left to enroll

862 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