SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot GHicks's Avatar
    Join Date
    Oct 2003
    Location
    Prescott Valley, Arizona, United States
    Posts
    135
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Update MySQL table from another table

    I inherited a database from an old project and would like to make the data more robust. Currently it stores a group of Jobs and each job has a customer listed, however the customer name is stored as a string value and there is a separate "Customer" table that I would prefer to instead of a join of the customer number rather than their name as this causes the data to not be as clean as the user could enter in whatever they like so we could have jobs listed under "ABC Widgets" and "ABC Widgets, Co" and they would appear to be 2 different customers.

    I've created a new field in the Jobs table to store the customer ID from the customer table, what I am now trying to figure out is how best to build the query to populate this new field for all the existing jobs (I'll be rewriting the other parts of the code to utilize customer numbers for additions/updates). So from my PHP Admin what would the query be to place the customer ID from the Customers table into the Jobs table?

    UPDATE 'Jobs' SET CustNum = Customer.ID WHERE Customer.Name = Jobs.CustomerName is where I am starting, but I think I am missing something. Most likely a JOIN (which I am still having some trouble fully grasping). Or is a JOIN even required since I can just call from the different tables? I want this to iterate through all the jobs and if it can't find a match, then just leave the CustNum field blank so I can manually resolve those.

    Greg
    Greg Hicks
    Tekamba Computers, LLC
    greg@tekamba.com
    http://tekamba.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yes, a join is required
    Code:
    UPDATE jobs
    INNER
      JOIN customer
        ON customer.name = jobs.customername 
       SET jobs.custnum = customer.id
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot GHicks's Avatar
    Join Date
    Oct 2003
    Location
    Prescott Valley, Arizona, United States
    Posts
    135
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks! That did exactly what I needed.
    Greg
    Greg Hicks
    Tekamba Computers, LLC
    greg@tekamba.com
    http://tekamba.com


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
  •