The UPDATE statement is quite powerful and easy to use usually. But there are times where you want to update your table using values from another table.
We will show you three different ways to do it: MERGE, SELECT and JOIN.
Contents for SQL: Update From Another Table Example
Starting point
For this example we will use again the model from the database first approach of entity framework.
Suppose a change in the robot naming convention. Now the robot dogs will be named after their factory name.
SELECT
By far the easiest to remember and to use.
Usually you can use the SELECT statement in most of the DBMS, so it can be the right choice if you’re not concerned with performances.
SET RobotDog.Name = f.Name
FROM
(
SELECT RobotFactoryId, Name
FROM RobotFactory
) f
WHERE RobotDog.RobotFactoryId = f.RobotFactoryId
JOIN
SET RobotDogAlias.Name = RobotFactoryAlias.Name
FROM
RobotDog AS RobotDogAlias
INNER JOIN RobotFactory AS RobotFactoryAlias
ON RobotDogAlias.RobotFactoryId = RobotFactoryAlias.RobotFactoryId
[WHERE Optional...]
The use of aliases is just to show them, they were not necessary.
MERGE
If you aim to increase your project performance, try testing the use of the MERGE STATEMENT.
USING RobotFactory F
ON D.RobotFactoryId = F.RobotFactoryId
WHEN MATCHED THEN
UPDATE
SET Name = F.Name