I have two tables called it_systems and departments. The it_systems table contains all the department names but I want to add in all their ID's so I can remove the deprtmentNames column. so I need to compare the name with the name in the department table and then insert the departments.id into the it_systems.departmentid but seems to be falling flat as MySQL is not that great. This is how far I have got
UPDATE
it_system_additional INNER JOIN
(
SELECT departmentName
FROM it_system_additional
) departments ON departments.DepartmentName = it_system_additional.departmentName
SET it_system_additional.departmentid = departments.departmentId
but I keep getting this error [Err] 1054 - Unknown column 'departments.departmentId' in 'field list' even though I know that column does exist.
Any help will be greatly appreciated. Thanks in advance. Andrea
User below query to achieve this:
UPDATE it_system_additional AS i
INNER JOIN departments AS d ON i.DepartmentName = d.departmentName
SET i.departmentid = d.departmentId
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments