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
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.
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
SELECT RobotFactoryId, Name
WHERE RobotDog.RobotFactoryId = f.RobotFactoryId
SET RobotDogAlias.Name = RobotFactoryAlias.Name
RobotDog AS RobotDogAlias
INNER JOIN RobotFactory AS RobotFactoryAlias
ON RobotDogAlias.RobotFactoryId = RobotFactoryAlias.RobotFactoryId
The use of aliases is just to show them, they were not necessary.
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
SET Name = F.Name