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

timstring;11023447 wrote:
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.

timstring;11023447 wrote:
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.