SQL UPDATE

SQL UPDATE statement is used to completely change or modify existing data rows in a table.

SQL UPDATE Statement

Syntax for SQL UPDATE Command

UPDATE table_name 
SET column1 = value1,
column2 = value2, ...
[WHERE condition]
In above syntax,

table_name - name of table which has to be updated.

column1, column2 are the names of columns which need to be changed.

value1, value2 are the new values to set.

To update only specific rows you can use the WHERE clause, examples are given below on how to do that.

NOTE:In the sql update statement, WHERE clause identifies the rows which need to be modified. If you do not include the WHERE clause, the specified column values for all the rows get updated.

Example 1

Consider this employee table given below

 ----- --------- ----- --------------- ---------- 
| ID | NAME | AGE | DEPARTMENT | SALARY |
----- --------- ----- --------------- ----------
| 10 | John | 32 | science | 2000.00 |
| 11 | Dwayne | 25 | mathematics | 1500.00 |
| 12 | Roman | 23 | physics | 2000.00 |
| 13 | Robert | 25 | physics | 6500.00 |
| 14 | Rick | 27 | arts | 8500.00 |
| 15 | Daryl | 22 | science | 4500.00 |
| 16 | Negan | 24 | science | 10000.00 |
---- ---------- ----- --------------- ----------
To change the department of certain employee whose id number is 12 from 'employee' table to 'science' the SQL UPDATE query would be like
UPDATE employee 
SET department ='science'
WHERE id = 12;
After execution of above query the table would look like
 ----- --------- ----- --------------- ---------- 
| ID | NAME | AGE | DEPARTMENT | SALARY |
----- --------- ----- --------------- ----------
| 10 | John | 32 | science | 2000.00 |
| 11 | Dwayne | 25 | mathematics | 1500.00 |
| 12 | Roman | 23 | science | 2000.00 |
| 13 | Robert | 25 | physics | 6500.00 |
| 14 | Rick | 27 | arts | 8500.00 |
| 15 | Daryl | 22 | science | 4500.00 |
| 16 | Negan | 24 | science | 10000.00 |
---- ---------- ----- --------------- ----------

Example 2

To increase the salaries of all the employees by 50% of the current salary, the query would be like
UPDATE employee 
SET salary = salary (salary * 0.5);
After execution of above query the table would look like
 ----- --------- ----- --------------- ---------- 
| ID | NAME | AGE | DEPARTMENT | SALARY |
----- --------- ----- --------------- ----------
| 10 | John | 32 | science | 3000.00 |
| 11 | Dwayne | 25 | mathematics | 2250.00 |
| 12 | Roman | 23 | physics | 3000.00 |
| 13 | Robert | 25 | physics | 9750.00 |
| 14 | Rick | 27 | arts | 12750.00 |
| 15 | Daryl | 22 | science | 6750.00 |
| 16 | Negan | 24 | science | 15000.00 |
---- ---------- ----- --------------- ----------
Prev - SQL INSERT Statement Clause

Next* SQL DELETE Statement


Comments