SQL Server: Update data in a Table using UPDATE Statement

SQL Server supports the standard SQL to update the data in the table. Use the UPDATE TABLE statement to update records in the table in SQL Server.

Syntax:

UPDATE table_name SET column_name1 = new_value, column_name2 = new_value, ... [WHERE Condition];

Note that the WHERE clause is optional, but you should use it to update the specific record. An UPDATE statement without the WHERE clause will update values in all the rows of the table.

Here we will update data in the Employee table shown below.

The following UPDATE statement will change the value of the Email column in the Employee table EmployeeID is 1.

UPDATE Employee
SET email = '[email protected]'
WHERE EmployeeID = 1;

Now, the Select * from Employee query will display the following result.

Update Multiple Columns

Using the UPDATE statement, You can update multiple columns by specifying multiple column-name = value separated by a comma, as shown below.

UPDATE Employee
SET Email = '[email protected]', Phone = '111.111.0007', HireDate='05-23-2001'
WHERE EmployeeID = 3;

Now, the Select * from Employee query will display the following result.

Note: If there are no matching records for the specified WHERE clause, then the UPDATE statement will not update any records.

You can update columns with the calculated values. The following will increase the salaries of all the employees to 10% in the Employee table using a single UPDATE statement.

UPDATE Employee 
SET Salary = Salary + (Salary * 10/100);

Now, the Select * from Employee query will display the following result.

You can use the subquery to update the data in one table from another table. The following UPDATE statement will update the Salary in the Consultant table by selecting Salary from the Employee table for the matching EmployeeID values.

UPDATE Consultant
SET salary = (SELECT salary
FROM Employee 
WHERE Employee.EmployeeID = Consultant.ConsultantID);

Note:

• The subquery must return a sign column value; otherwise, an error will be raised.
• If the subquery could not find any matching row, then the value will be updated to NULL.