UPDATE Statement when updating one table with data from another table

SQL Server

http://www.techonthenet.com/sql_server/update.php

SQL Server: UPDATE Statement

This SQL Server tutorial explains how to use the UPDATE statement in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) UPDATE statement is used to update existing records in a table in a SQL Server database. There are 3 syntaxes for the UPDATE statement depending on whether you are performing a traditional update or updating one table with data from another table.

Syntax

The syntax for the UPDATE statement when updating one table in SQL Server (Transact-SQL) is:

UPDATE table
SET column1 = expression1,
    column2 = expression2,
    ...
[WHERE conditions];

OR

The syntax for the UPDATE statement when updating one table with data from another table in SQL Server (Transact-SQL) is:

UPDATE table1
SET column1 = (SELECT expression1
               FROM table2
               WHERE conditions)
[WHERE conditions];

OR

The syntax for the SQL Server UPDATE statement when updating one table with data from another table is:

UPDATE table1
SET table1.column = table2.expression1
FROM table1
INNER JOIN table2
ON (table1.column1 = table2.column1)
[WHERE conditions];

Parameters or Arguments

column1, column2

The columns that you wish to update.

expression1, expression2

The new values to assign to the column1, column2. So column1 would be assigned the value of expression1, column2 would be assigned the value of expression2, and so on.

WHERE conditions

Optional. The conditions that must be met for the update to execute.

Example - Update single column

Let's look at a very simple SQL Server UPDATE query example.

For example:

UPDATE employees
SET last_name = 'Johnson'
WHERE employee_id = 10;

This SQL Server UPDATE example would update the last_name to 'Johnson' in the employees table where the employee_id is 10.

Example - Update multiple columns

Let's look at a SQL Server UPDATE example where you might want to update more than one column with a single UPDATE statement.

For example:

UPDATE employees
SET first_name = 'Kyle',
    employee_id = 14
WHERE last_name = 'Johnson';

When you wish to update multiple columns, you can do this by separating the column/value pairs with commas.

This SQL Server UPDATE statement example would update the first_name to 'Kyle' and the employee_id to 14 where the last_name is 'Johnson'.

Example - Update table with data from another table

Let's look at an UPDATE example that shows how to update a table with data from another table in MySQL.

For example:

UPDATE employees
SET first_name = (SELECT first_name
                  FROM contacts
                  WHERE contacts.last_name = employees.last_name)
WHERE employee_id > 95;

This UPDATE example would update only the employees table for all records where the employee_id is greater than 95. When the last_name from the contacts table matches the last_name from the employees table, the first_name from the contacts table would be copied to the first_name field in the employees table.

You could rewrite this UPDATE statement in SQL Server using the second syntax to update a table with data from another table.

For example:

UPDATE employees
SET employees.first_name = contacts.first_name
FROM employees
INNER JOIN contacts
ON (employees.last_name = contacts.last_name)
WHERE employee_id > 95;

This UPDATE example would perform the same update as the previous.