MS SQL Server - How to UPDATE from SELECT statement?

You have two tables: table_b contains values that you want to update the table_a with.

Using SQL Query, there are two common ways to do UPDATE from SELECT statement:

1. Using INNER JOIN

This is the most common solution.

For example, we have two tables:

Table students with four columns: name, student_id, batch, score:

name	student_id	batch
Alex	1	1
Anna	2	2
Bob	3	1
Mike	4	1
John	5	1
Stacey	6	2

Table exam_results with two columns: student_id, score:

student_id	score
1	10
2	9
3	9
4	7
5	10
6	6

The score column in the table students is still empty.

Now you want to update it with the score values from table exam_results, for those students in batch 1:

UPDATE
    students
SET
    students.score = exam_results.score
FROM
    students
    INNER JOIN exam_results ON students.student_id = exam_results.student_id
WHERE
    batch = 1;

So the students table will be updated to:

name	student_id	batch	score
Alex	1	1	10
Anna	2	2
Bob	3	1	9
Mike	4	1	7
John	5	1	10
Stacey	6	2

If you want to update the whole table, omit the last WHERE condition:

UPDATE
    students
SET
    students.score = exam_results.score
FROM
    students
    INNER JOIN exam_results ON students.student_id = exam_results.student_id;

And the result will be:

name	student_id	batch	score
Alex	1	1	10
Anna	2	2	9
Bob	3	1	9
Mike	4	1	7
John	5	1	10
Stacey	6	2	6

2. Using MERGE

MERGE is another solution which was introduced by Microsoft from SQL Server 2008. It works like INNER JOIN but you can perform UPDATE and INSERT altogether.

Back to the example of two tables students and exam_results above, we can solve it using MERGE:

MERGE INTO 
    students
USING 
    exam_results 
ON 
    students.student_id = exam_results.student_id
WHEN MATCHED THEN
    UPDATE
        SET 
            students.score = exam_results.score
WHEN NOT MATCHED THEN
    INSERT 
            (student_id, score)
    VALUES
            (exam_results.student_id, exam_results.score);

It still updates the column score in table student with the values of column score from the table exam_results, but it adds one more thing. It performs something like INSERT IF NOT EXIST.

If some records from exam_results do not match with any record from students, it will insert these records as new rows into student.

For example, table students has 6 records only:

name	student_id	batch
Alex	1	1
Anna	2	2
Bob	3	1
Mike	4	1
John	5	1
Stacey	6	2

But table exan_results has 7 records:

student_id	score
1	10
2	9
3	9
4	7
5	10
6	6
7	8

The result of the MERGE statement above will be:

name	student_id	batch	score
Alex	1	1	10
Anna	2	2	9
Bob	3	1	9
Mike	4	1	7
John	5	1	10
Stacey	6	2	6
	7		8

Need a good GUI Tool for MS SQL Server? Try TablePlus, a modern, native tool for multiple relational databases. It’s free anyway.