UPDATE Statement is used in SQL to change existing records in a table. This way, we can change one record, multiple records or all of them. Condition based on which are records restricted is placed in WHERE clause.

Syntaxe – 3 Ways of UPDATE Usage

A) Simple form: Change of the values in certain column or columns based on condition

UPDATE dbo.Table
SET <Column 1> = <New Value For Column 1>,
<Column 2> = <New Value For Column 2>
WHERE <Condition>;

B) Using JOIN: Change of the values based on condition from another table

UPDATE dbo.Table_A
SET <Column Table_A> = <New Value For Column From Table_A>
FROM dbo.Table_A INNER JOIN dbo.dbo.Table_B
                   ON  dbo.Table_A.ID = dbo.Table_B.ID
WHERE <Condition on Column From Table_B>;

C) with OUTPUT clause: if we want to log changes above the table. Export can be simply saved into another table (e.g. as a backup)

UPDATE dbo.Table
SET <Column 1>= <New Value For Column 1>
OUTPUT inserted.<Column 1>, deleted.<Column 1>
WHERE <Condition>;

Output clause makes it possible to show/export all changed records as a result of command. We use prefix “inserted.” to get new values of changed records. Using prefix “deleted.” gives us original record values (prior update).

(!!!) If you do not use the WHERE clause, changes occur over the entire table

UPDATE – Practical Examples

1 Simple form of table change example

The easiest way is to change records in the table directly (as in A). Using SQL command SET distinguishes new values for record/s and conditions in clause WHERE.

Change of one record in table:

UPDATE dbo.Salary
SET Employee_Salary = 5000
WHERE Employee = 'XYZ';

Change of multiple records in table:

UPDATE dbo.Salary
SET Employee_Salary = 5000
WHERE Employee IN ('XYZ','HHH');


Another option how to make a change in a table is change of table with application of condition from another table. We will use it mainly when we want to change multiple records in relational database in which the criterion for changed records is located in different table which we will Join for these purposes.

I changed the wage to USD 5000 in the previous table. Let’s say I would like to raise wage for all people on the business intelligence developer by USD 1000. Information on employees is located in table dbo.Job


UPDATE dbo.Salary
SET dbo.Salary.Employee_Salary = dbo.Salary.Employee_Salary + 1000
FROM dbo.Job
WHERE dbo.Job.ID_Job = dbo.Job.ID_Job
AND dbo.Job.Employee_Job= 'Business intelligence developer';

UPDATE using FROM/INNER JOIN (more on joins in article SQL INNER JOIN)

UPDATE dbo.Salary
SET dbo.Salary.Employee_Salary = dbo.Salary.Employee_Salary + 10 000
FROM dbo.Salary INNER JOIN  dbo.Job
                   ON  dbo.Salary.ID_Job = dbo.Job.ID_Job
WHERE  dbo.Job.Employee_Job= 'Business intelligence developer';

3 UPDATE + OUTPUT clause

Let’s have table dbo.Salary where we have 2 employees: “XYZ” with wage USD 5125  and “HHH” with wage USD 5000.

Both employees will have their wage changed to 20000 with output sql clause:

UPDATE [Temp].[dbo].[Salary]
  SET [Salary] = 20000
  inserted.[Salary] AS New_Salary,
  deleted.[Salary] AS Old_Salary

Rate this post

Ing. Jan Zedníček - Data Engineer & Controlling

My name is Jan Zedníček and I have been working as a freelancer for many companies for more than 10 years. I used to work as a financial controller, analyst and manager at many different companies in field of banking and manufacturing. When I am not at work, I like playing volleyball, chess, doing a workout in the gym.

🔥 If you found this article helpful, please share it or mention me on your website

Leave a Reply

Your email address will not be published. Required fields are marked *