Update from Another Table in SQL

I’m always finding myself in the same position: I have a table with two or more columns. I need to fill in one of those columns based on another one. However, the data I need is in a separate table.

Here is an example of this scenario. Say we have two tables with two columns each.

Table: people

id	name
0	Donald Smith
1	Louis Clark
2	Henry Curtin
3	Jeffrey Odell
4	Thelma Logan

Table: scores

personId	score
2	77
3	78
1	99
4	79
0	88

Each record in the people table has an id and a name. Each record in the scores table has a personId which is linked people.id and a score.

If we wanted to retrieve data containing names next to scores, we could do this easily with a JOIN:

SELECT    p.name,
          s.score
FROM      people p
JOIN      scores s
ON        p.id = s.personId

name	score
Donald Smith	88
Louis Clark	99
Henry Curtin	77
Jeffrey Odell	78
Thelma Logan	79

But what happens when our project calls for a change: names are now required to be stored in the scores table. After adding the new column, how do we insert the data?

personId	score	name
2	77	NULL
3	78	NULL
1	99	NULL
4	79	NULL
0	88	NULL

We need to update one table based on another. This can be solved using an UPDATE with a JOIN.

MSSQL

UPDATE        scores
SET           scores.name = p.name
FROM          scores s
INNER JOIN    people p
ON            s.personId = p.id

MySQL

UPDATE    scores s,
          people p
SET       scores.name = people.name
WHERE     s.personId = p.id

And our scores table is complete!

personId	score	name
2	77	Henry Curtin
3	78	Jeffrey Odell
1	99	Louis Clark
4	79	Thelma Logan
0	88	Donald Smith

The commands above work for this specific scenario. For more generic versions of the same command, see below.

Generic MSSQL

UPDATE        targetTable
SET           targetTable.targetColumn = s.sourceColumn
FROM          targetTable t
INNER JOIN    sourceTable s
ON            t.matchingColumn = s.matchingColumn

Generic MySQL

UPDATE    targetTable t,
          sourceTable s
SET       targetTable.targetColumn = sourceTable.sourceColumn
WHERE     t.matchingColumn = s.matchingColumn