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