使用一个表来更新另一个SQL(Use one table to update another SQL)

使用一个表来更新另一个SQL(Use one table to update another SQL)

我在从一个表更新另一个表时遇到问题。 我希望SQL从CompanyEmployees中的两个EmployeeNum字段相同的数据更新Employees的行。 此外，如果EmployeeNum存在EmployeeNum存在，那么我需要在Employees I中创建一个新行，到目前为止，我已经尝试了两个表的连接。

SELECT Employees.PhoneNum, Employees.Data, CompanyEmployees.PhoneNum, CompanyEmployees.SystemData FROM CompanyEmployees INNER JOIN Employees ON CompanyEmployees.Employees=Techs.EmployeeNum

我在两个表中都获得了正确的列数据，但我没有更新Employees 。 我需要在某处进行INSERT或UPDATE吗？ 如何将CompanyEmployees的整行数据插入Employees中CompanyEmployees.EmployeeNum中不存在CompanyEmployees.EmployeeNum Employees ？

我需要这样做，因为CompanyEmployees只是一个电话簿， Employees有电话号码和更多信息。 但是CompanyEmployees雇员在里面招聘了不属于Employees新Employees 。

I have having problems updating one table from another. I want SQL to update the rows in Employees from the data in CompanyEmployees where the two EmployeeNum fields are the same. Also if an EmployeeNum exists inside of CompanyEmployees that doesn't match one in Employees then I need a new row created in Employees I so far have tried a join for the two tables.

SELECT Employees.PhoneNum, Employees.Data, CompanyEmployees.PhoneNum, CompanyEmployees.SystemData FROM CompanyEmployees INNER JOIN Employees ON CompanyEmployees.Employees=Techs.EmployeeNum

I get the right column data in both tables but i doesnt update Employees. Do I need an INSERT or UPDATE somewhere? How can I insert the whole row of data from CompanyEmployees into Employees where CompanyEmployees.EmployeeNum doesn't exist in Employees?

I need to do this because CompanyEmployees is only a phone directory and Employees has phone numbers and more information. But CompanyEmployees has new hires inside it that are not inside Employees.

最满意答案

记住RDBMS的R是关系型的。 它们是为关系而建立的。 要使用CompanyEmployees表更新Employees表，您可以使用以下内容：

INSERT INTO Employees (columns) VALUES (SELECT columns FROM CompanyEmployees) ON DUPLICATE KEY UPDATE

您可能甚至不需要“员工”，因为它是“公司雇员”的超集。 我建议合并两个表或至少将数据移动到一个表中。

Remember RDBMS has the R for relational. They are built for relationships. To update you Employees table with the CompanyEmployees table you could use something like the below:

INSERT INTO Employees (columns) VALUES (SELECT columns FROM CompanyEmployees) ON DUPLICATE KEY UPDATE

You probably do not even need 'Employees' as it is a superset of 'CompanyEmployees'. I would suggest looking to merge the two tables or at least move the data into a single table.