SQL - UPDATE Statement

The UPDATE TABLE statement is used to update records of the table in the database.

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.

For the demo purpose, the following Employee table will be used in all examples here.

The following UPDATE statement will change the value of the Email column of the Employee table where the value of the EmpId is 1 in SQL Server, Oracle, MySQL, PostgreSQL, SQLite database.

UPDATE Employee
SET email = 'jking@test.com'
WHERE EmpId = 1;

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

Update Multiple Columns

Using the UPDATE statement, you can update more than one column of a table. The following query will change the Email and the PhoneNo in the Employee table whose EmpIdis 2.

UPDATE Employee
SET Email = 'jb007@test.com', PhoneNo = '111.111.0007'
WHERE EmpId = 2;

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

You can also update a column with the calculated value. 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 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 EmpId values.

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