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.
EmpId | FirstName | LastName | PhoneNo | Salary | |
---|---|---|---|---|---|
1 | 'John' | 'King' | '[email protected]' | '123.123.1834' | 33000 |
2 | 'James' | 'Bond' | |||
3 | 'Neena' | 'Kochhar' | '[email protected]' | '123.456.4568' | 17000 |
4 | 'Lex' | 'De Haan' | '[email protected]' | '123.456.4569' | 15000 |
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 = '[email protected]'
WHERE EmpId = 1;
Now, the Select * from Employee
query will display the following result.
EmpId | FirstName | LastName | PhoneNo | Salary | |
---|---|---|---|---|---|
1 | 'John' | 'King' | '[email protected]' | '123.123.1834' | 33000 |
2 | 'James' | 'Bond' | |||
3 | 'Neena' | 'Kochhar' | '[email protected]' | '123.456.4568' | 17000 |
4 | 'Lex' | 'De Haan' | '[email protected]' | '123.456.4569' | 15000 |
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 EmpId
is 2.
UPDATE Employee
SET Email = '[email protected]', PhoneNo = '111.111.0007'
WHERE EmpId = 2;
Now, the Select * from Employee
query will display the following result.
EmpId | FirstName | LastName | PhoneNo | Salary | |
---|---|---|---|---|---|
1 | 'John' | 'King' | '[email protected]' | '123.123.1834' | 33000 |
2 | 'James' | 'Bond' | [email protected] | 111.111.0007 | |
3 | 'Neena' | 'Kochhar' | '[email protected]' | '123.456.4568' | 17000 |
4 | 'Lex' | 'De Haan' | '[email protected]' | '123.456.4569' | 15000 |
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.
EmpId | FirstName | LastName | PhoneNo | Salary | |
---|---|---|---|---|---|
1 | 'John' | 'King' | '[email protected]' | '123.123.1834' | 36300 |
2 | 'James' | 'Bond' | [email protected] | 111.111.0007 | |
3 | 'Neena' | 'Kochhar' | '[email protected]' | '123.456.4568' | 18700 |
4 | 'Lex' | 'De Haan' | '[email protected]' | '123.456.4569' | 16500 |
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);