SQL: Update From Another Table Example

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.

database-first-edmx

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.

UPDATE RobotDog
SET RobotDog.Name = f.Name
FROM
(
    SELECT RobotFactoryId, Name
    FROM RobotFactory
) f
WHERE RobotDog.RobotFactoryId = f.RobotFactoryId

JOIN

UPDATE RobotDogAlias
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.

MERGE INTO RobotDog D
USING RobotFactory F
ON D.RobotFactoryId = F.RobotFactoryId
WHEN MATCHED THEN
    UPDATE
        SET Name = F.Name