Use one table to update another.
Results 1 to 6 of 6

Thread: Use one table to update another.

  1. #1
    Senior Member
    Join Date
    Sep 2012
    Posts
    269

    Use one table to update another.

    I am unsuccessfully trying to update one table from another. I want SQL to update the rows in `Techs` from the data in `CourtneyTechs` where the two `TechNum` fields are the same. Also, if a `TechNum` exists in `CourtneyTechs` that does not match one in `Techs`, I want a new row created in `Techs`. Here's what I've tried to join the two tables together:

    PHP Code:
    SELECT Techs.CellNumTechs.LongSystemCourtneyTechs.CellNumCourtneyTechs.System
    FROM CourtneyTechs
    INNER JOIN Techs
    ON CourtneyTechs
    .TechNum=Techs.TechNum 
    This returns the right column data in both tables, but doesn't update Techs. Am I missing an INSERT or UPDATE somewhere?

    How would I go about inserting the whole row data from `CourtneyTechs` into `Techs` where `CourtneyTechs.TechNum` does not exist in `Techs`?
    Last edited by timstring; 01-30-2013 at 05:38 PM.

  2. #2
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,428
    Quote Originally Posted by timstring View Post
    This returns the right column data in both tables, but doesn't update Techs. Am I missing an INSERT or UPDATE somewhere?
    Er... well, yeah. If you expect to INSERT or UPDATE row(s), then you must execute an INSERT or UPDATE query, respectively. Executing a SELECT query will only... select data.

    Quote Originally Posted by timstring View Post
    How would I go about inserting the whole row data from `CourtneyTechs` into `Techs` where `CourtneyTechs.TechNum` does not exist in `Techs`?
    Why are you trying to duplicate information from one table into another? While it may be possible, it sounds like you've got a bad database design. The 'R' in RDBMS stands for relational, not redundant, after all.

  3. #3
    Senior Member
    Join Date
    Sep 2012
    Posts
    269
    I need to merge the tables because `CourtneyTechs` is only a phone directory. `Techs` has phone numbers and more info. However, `CourtneyTechs` has new hires in it that I don't have in `Techs`. Would you still keep two tables?

  4. #4
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,428
    If the information in Techs is a superset that completely contains the information in CourtneyTechs (apparently just phone numbers and... names? IDs? something?), then why does CourtneyTechs exist in the first place? Sounds like you should only have Techs.

  5. #5
    Senior Member
    Join Date
    Sep 2012
    Posts
    269
    `CourtneyTechs` exists because my supervisor Courtney had produced an updated phone list and she gave me the excel (egad) spreadsheet she used for her data. My table `Techs` already existed, but at the moment, I'm not set up for anyone to modify my databases other than yours truly.

    So, will you answer my question: How can I update my `Tech` table with the `CourtneyTechs` table?

  6. #6
    Senior Member Derokorian's Avatar
    Join Date
    Apr 2011
    Location
    Denver
    Posts
    1,783
    Code:
    INSERT INTO Techs (columns) VALUES (SELECT columns FROM CourtneyTechs) ON DUPLICATE KEY UPDATE
    Something like that should do the trick.
    Sadly, nobody codes for anyone on this forum. People taste your dishes and tell you what is missing, but they don't cook for you. ~anoopmail
    I'd rather be a comma, then a full stop.
    User Authentication in PHP with MySQLi - Don't forget to mark threads resolved - MySQL(i) warning

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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