SQL UPDATE Statement – Syntax and Example of Updating Records

Last modified date:

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');

2 UPDATE + FROM + JOIN

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 using FROM

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
OUTPUT
  inserted.[ID_Job],
  inserted.[Employee],
  inserted.[Salary] AS New_Salary,
  deleted.[Salary] AS Old_Salary
WHERE 1=1

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Category: Handling SQL tables Tags:

About Ing. Jan Zedníček - BI Developer, Finance controller

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.