Summary -

In this topic, we described about the UPDATE Statement with detailed example.

UPDATE statement is used to UPDATE the column values in the existing table. WHERE clause is used in the UPDATE statement to update the matched rows that satisfies the condition in the WHERE clause. If we try to UPDATE all the rows with same value then there is no need to specify WHERE clause in the UPDATE statement.

Syntax -

UPDATE
table_name | view | synonym
[AS temp_table_name ]
SET column1 = value1, column2 = value2, …, column = valueN
WHERE Condition; 

Example –

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Update one column value.

Requirement – Update the salary of an employee whose emp id is 009. The query was as follows –

UPDATE employee_details SET salary = 17000 WHERE emp_id = 009; 

By executing above query, we can update the salary of salesman whose emp id is 009 with 17000. The table after update was as follows –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 17000.00 2000

Scenario – Update multiple column values.

Requirement – Update the manager_id and dept_id values of employee whose designation is "Clerk". The query was as follows –

UPDATE employee_details
  SET manager_id = 'Employee5', dept_id = 2000
WHERE designation = "Clerk"; 

By executing above query, we can update the manager_id and department values of employee on employee_details whose designation is Clerk. The table after update was as follows -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee5 2019-07-11 15000.00 2000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Update more than one row.

Requirement – Update the dept_id of multiple of employees whose manager_id is Employee3. The query was as follows –

UPDATE employee_details
  SET dept_id = 2000
WHERE manager_id = "Employee3"; 

By executing above query, we can update the department id of multiple employees whose manager id is "Employee3". The table after update was as follows –

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 2000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 2000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 2000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Update all rows of single column values.

Requirement – Update date_of_hire of all employees with "2019-07-11". The query was as follows –

UPDATE employee_details SET date_of_hire = "2019-07-11"; 

By executing above query, we can update the date_of_hire of all employees with 2019-07-11. The table after update was as follows –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-07-11 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-07-11 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-07-11 14000.00 2000
009 Employee9 Salesman Employee4 2019-07-11 13000.00 2000