Use one table to update another.

[timstring]

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:

SELECT Techs.CellNum, Techs.LongSystem, CourtneyTechs.CellNum, CourtneyTechs.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`?

[bradgrafelman]

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.

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.

[timstring]

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?

[bradgrafelman]

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.

[timstring]

`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?

[Derokorian]

INSERT INTO Techs (columns) VALUES (SELECT columns FROM CourtneyTechs) ON DUPLICATE KEY UPDATE

Something like that should do the trick.