Inner join updates

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';

ORACLE:

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).

MySQL:

UPDATE Employee INNER JOIN Department
    ON Employee.DepD = Department.DepID
   SET t.Pay = t.Pay * 1.2
 WHERE Department.Name = 'Sales';