This article shows how a SQL update statement can change a table.
A Brief Introduction to the UPDATE Query in Microsoft SQL Server
T-SQL (Transact-SQL) update is a command used to modify data in a SQL Server database. It is used to change one or more rows in a table by updating existing values or inserting new values into the table. The syntax for the T-SQL update statement is as follows:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;In this syntax:
Table_name is the name of the table that you want to update.
Column1, column2, etc. are the columns that you want to update.
Value1, value2, etc. are the new values that you want to set for the corresponding columns.
WHERE clause specifies the condition that the rows must meet in order to be updated. If you omit the WHERE clause, all rows in the table will be updated.
Versions Of SQL Server
There are differences in the behavior and syntax of UPDATE statements across different versions of SQL Server.
Here are a few examples:
SQL Server 2000
SQL Server 2000 and earlier versions required that all tables in the FROM clause be included in a SET statement to be updated. For example, if you had a join between two tables in the FROM clause, both tables needed to be listed in the SET statement. In later versions of SQL Server, this is not required and you can update only the columns you need to.
SQL Server 2005
SQL Server 2005 introduced the OUTPUT clause, which allows you to return information from the UPDATE statement. This can be useful for logging or auditing purposes, or for cascading updates to related tables.
SQL Server 2008
SQL Server 2008 introduced the MERGE statement, which allows you to perform multiple UPDATE, INSERT, and DELETE operations in a single statement. This can be useful for synchronizing data between tables or databases.
Starting with SQL Server 2012
Starting with SQL Server 2012, you can update a column with a computed value by using the UPDATE statement with the SET clause and the = expression syntax. This allows you to update a column with a value that is computed from other columns in the same row.
These are just a few examples, and there are many other differences in syntax and behavior across different versions of SQL Server. It's always a good idea to consult the documentation for your specific version to ensure that you're using the correct syntax and to take advantage of any new features or improvements.
For example, the following T-SQL update statement updates the "salary" column of the "employees" table for the employee with an "employee_id" of 123:
CREATE TABLE Employee (
Name VARCHAR(50) NOT NULL,
Age INT NOT NULL,
Dept VARCHAR(4) NOT NULL,
Gender VARCHAR(2) NOT NULL,
PRIMARY KEY (Name)
);
INSERT INTO Employee (Name, Age, Dept, Gender)
VALUES ('John Smith', 35, 'Acct', 'M'),
('Jane Doe', 27, 'HR', 'F'),
('Mark Johnson', 42, 'Mktg', 'M'),
('Sarah Lee', 29, 'Ops', 'F'),
('Alex Kim', 25, 'Acct', 'M'),
('Lena Chen', 31, 'Mktg', 'F'),
('Sam Lee', 45, 'Ops', 'M'),
('Taylor Smith', 24, 'HR', 'F'),
('Jamie Lee', 38, 'Ops', 'NB'),
('Avery Chen', 33, 'Mktg', 'NB');To modify the age of an employee, we can use the UPDATE statement in T-SQL.
For example, to increase the age of John Smith to 36, we can use the following script:
UPDATE Employee
SET Age = 36
WHERE Name = 'John Smith';
This will modify the age of John Smith to 36 in the Employee table.
If we want to update the age of all employees in a particular department, we can use the following script:
UPDATE Employee
SET Age = Age + 1
WHERE Dept = 'Mktg';
This will increase the age of all employees in the Mktg department by 1.
Note that the WHERE clause is used to specify which rows should be updated. If the WHERE clause is omitted, all rows in the table will be updated. Therefore, it is important to be careful when using the UPDATE statement and to always include a WHERE clause to ensure that only the intended rows are modified.
Update SQL examples to modify existing data
SQL UPDATE syntax with subqueries and JOIN statement
here's an example of creating a Department table that is related to the Employee table, followed by an example of a SQL UPDATE statement with subqueries and a JOIN statement.
First, let's create the Department table:
CREATE TABLE Department (
DeptCode VARCHAR(4) NOT NULL PRIMARY KEY,
DeptName VARCHAR(50) NOT NULL
);Next, we can insert some sample data into the Department table:
INSERT INTO Department (DeptCode, DeptName)
VALUES ('Acct', 'Accounting'),
('HR', 'Human Resources'),
('Mktg', 'Marketing'),
('Ops', 'Operations');Now we can add a foreign key constraint to relate the Employee table with the Department table:
ALTER TABLE Employee
ADD CONSTRAINT FK_Employee_Dept
FOREIGN KEY (Dept) REFERENCES Department(DeptCode);To update the Employee table using subqueries and a JOIN statement, let's say we want to update the Age column of employees who work in the "Marketing" department to be 35 years old. We can use the following UPDATE statement:
UPDATE Employee
SET Age = 35
WHERE Dept = (
SELECT DeptCode FROM Department WHERE DeptName = 'Marketing'
);This statement first selects the DeptCode for the "Marketing" department from the Department table using a subquery, and then uses a JOIN statement with the Employee table to update the Age column of employees who work in that department.
Update With A Join
Here's an example T-SQL script that updates the Age column of the Employee table using a join with the Department table:
UPDATE Employee
SET Employee.Age = Employee.Age + 1,
Department.DeptName = 'New Department Name'
FROM Employee
INNER JOIN Department
ON Employee.Dept = Department.DeptCode
WHERE Employee.Gender = 'M';Sub Select
Here's an example of an update using a subquery to modify the Age column of the Employee table based on the values in the Department table:
UPDATE Employee
SET Age = (
SELECT AVG(Age)
FROM Employee
WHERE Dept = Department.DeptCode
)
FROM Department
WHERE Employee.Dept = Department.DeptCode;
In This example, I chose to show what the update messages looks like vs the results.
This query uses a subquery to calculate the average age for each department in the Department table, and then uses that value to update the Age column of the corresponding employees in the Employee table. The subquery is correlated with the outer query by the WHERE clause that matches the department codes in both tables.
Specifying a table alias as the target object
To specify a table alias as the target object in an UPDATE statement, you can use the alias name instead of the actual table name. Here's an example of updating the Employee table with a table alias e:
UPDATE e
SET Age = Age + 1
FROM Employee e
INNER JOIN Department d ON e.Dept = d.DeptCode
WHERE d.DeptName = 'Marketing';
In this example, the Employee table is aliased as e in both the UPDATE statement and the FROM clause. This allows you to reference the Employee table using the e alias throughout the query, making it more concise and easier to read.
SQL UPDATE multiple columns
Here's an example T-SQL update statement that modifies multiple columns in the Employee table:
UPDATE Employee
SET Age = 30,
Dept = 'SLS'
WHERE Name = 'John Smith';
In this example, the Age column is updated to 30 and the Dept column is updated to 'Sales' for the employee with the name 'John Smith'. You can modify the columns and conditions to fit your specific use case.
Here's an example of how to use a Common Table Expression (CTE) to update the Age column in the Employee table:
WITH EmployeeCTE AS (
SELECT Name, Age, Dept
FROM Employee
WHERE Dept = 'Mktg'
)
UPDATE EmployeeCTE
SET Age = Age + 1;
This script will increment the age of all employees in the Mktg department by 1.
The WITH clause defines the CTE named EmployeeCTE, which is a temporary result set that is defined within the context of the update statement. In this case, the CTE is used to select all employees in the Mktg department, and the UPDATE statement then modifies the Age column of the EmployeeCTE result set.
Note that this update statement is equivalent to the following:
UPDATE Employee
SET Age = Age + 1
WHERE Dept = 'Mktg';However, in more complex queries, CTEs can be useful for defining intermediate results that can then be used in multiple queries, and for simplifying the structure of the overall SQL script.
Locking behavior
When updating a table, the database management system (DBMS) will acquire locks on the affected data to ensure data consistency and prevent other transactions from accessing the data while it is being modified.
The exact locking behavior of an update statement can depend on the database management system being used, as well as the transaction isolation level and locking strategy being used. In general, the DBMS will try to acquire the least restrictive lock that is necessary to ensure data consistency.
For example, if you are updating a single row in a table, the DBMS might use a row-level lock to lock only that row while it is being updated. If you are updating multiple rows or the entire table, the DBMS might use a table-level lock to prevent other transactions from accessing the table while the update is in progress.
In some cases, the DBMS may escalate the locking from a row-level to a table-level lock, especially if the update statement is part of a larger transaction. Escalating locks can reduce contention and improve concurrency, but it can also increase the risk of deadlocks and other concurrency issues.
Overall, the locking behavior of an update statement can be complex and dependent on many factors. It is important to understand the locking behavior of your DBMS and the specific update statement you are using to ensure proper data consistency and avoid performance issues.
Update with Locking Hints
In SQL Server, you can use locking hints to control the level of locking used during an UPDATE statement. Locking hints specify the type of locks that are held on the affected data during the execution of the statement. Locking hints can be useful in situations where concurrent access to data may result in contention or conflicts.
Here are some examples of locking hints that can be used with an UPDATE statement:
ROWLOCK:
This hint specifies that row-level locks should be used instead of page- or table-level locks. This can reduce the scope of the lock and help to avoid blocking other transactions that may need to access the same table.
TABLOCK:
This hint specifies that a table-level lock should be used for the entire update statement. This can be useful for large updates that affect a significant portion of the table, as it can reduce the number of locks required and improve performance. However, it can also cause contention with other transactions that need to access the table.
UPDLOCK:
This hint specifies that update locks should be used during the execution of the statement. Update locks are held until the transaction is completed and can help to prevent conflicts with other transactions that may be attempting to modify the same data.
XLOCK:
This hint specifies that an exclusive lock should be obtained on the affected data during the execution of the statement. This can help to prevent other transactions from accessing the data during the update, but can also lead to contention and blocking.
It's important to use locking hints with care, as they can have a significant impact on the performance and concurrency of your system. You should test and monitor the effects of locking hints in your environment to ensure that they are achieving the desired results without causing unexpected side effects.
Setting Column Values
The Example in the section below demonstrates how to update a column using a computed value, and the data will be interpreted in a sub-query if necessary.
Specifying a subquery in the SET clause
In SQL Server, you can specify a subquery in the SET clause of an UPDATE statement to modify the column values. Here's an example of how you can use a subquery in the SET clause to update the Age of employees in the Employee table based on the average age of employees in the same department:
UPDATE e
SET e.Age = (SELECT AVG(Age) FROM Employee WHERE Dept = e.Dept)
FROM Employee e
In this example, the subquery (SELECT AVG(Age) FROM Employee WHERE Dept = e.Dept) calculates the average age of employees in the same department as the employee being updated. The subquery is included in the SET clause to update the Age column of the Employee table. The UPDATE statement updates the Employee table using the table alias e.
Additional Resources:
Final Notes:
For quick results in using SQL update multiple rows, updating multiple columns and avoiding higher level locks in your T-SQL connect with me in chat, e-mail or phone. I am happy to help you execute your update query in the best most efficient way possible
--Mike Bennyhoff