Here is how to do update of a table that needs to be joined with another table in MySQL, Oracle and SqlServer. Let’s say we want to give a 20% raise to sales department.
Microsoft SQL Server:
UPDATE [Employee] SET [Employee].[Pay] = [Employee].[Pay] * 1.2 FROM [Employee] INNER JOIN [Department] ON [Employee].[DepD] = [Department].[DepID] WHERE [Department].[Name] = 'Sales';
UPDATE ( SELECT Employee.Pay FROM Employee INNER JOIN Department ON Employee.DepID = Department.DepID WHERE Department.Name = 'Sales' ) t SET t.Pay = t.Pay * 1.2;
Be careful here to have a primary key set on Department.DepID. Otherwise you get an error ORA-01779 (cannot modify a column which maps to a non key-preserved table). This is because our subquery needs to return rows that match the rows in Employee table so it can do an update on Employee. If we got 2 or more rows for 1 row in Employee Oracle wouldn’t be able to determine how to do an update. So simply said, we need a “n to 1” mapping (for each Employee one or zero Departments).
UPDATE Employee INNER JOIN Department ON Employee.DepD = Department.DepID SET t.Pay = t.Pay * 1.2 WHERE Department.Name = 'Sales';