SQL Update

Update Statement is used to change existing values in a table or a view's base table.

The UPDATE statement is used to update existing records in a table.
 

Table               is the name of the table

Column            is the name of the column in the table to populate

Value               is the corresponding value or sub query for the column

Condition         identifies the rows to be updated and is composed of column names, expressions, constants, sub queries and comparison operators

Confirm the update operation by querying the table to display the updated rows.

For more information, see “UPDATE” in the Oracle Database SQL Reference.

Note: in general, use the primary key to identify a single row. Using other columns can

Unexpectedly cause several rows to be updated. For example, identify a single row in the EMPLOYEES table by name is dangerous, because more than one employee may have the same name.

 

Updating rows in a Table

Specific row or rows are modified if you specify the WHERE clause

UPDATE employees

SET department_id = 70

WHERE employee_id = 113;

1 row updated

All rows in the table are modified if you omit the WHERE clause:

UPDATE copy_emp

SET department_id = 110;

22 rows updated

  

If you omit the WHERE clause, all the rows in the table are modified.

SELECT last_name, department_id

FROM copy_emp;  

LAST_NAME	DEPARTMENT_ID
Whalen	110
Hartstien	110
Fay	110
Higgins	110
Gietz	110
Raphael	11

Note: The COPY_EMP table has the same data as the EMPLOYEES table.  

SQL UPDATE Syntax

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

Note: Notice the WHERE clause in the UPDATE syntax. The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!

 

SQL UPDATE Example

The "Persons" table:
 

P_Id	Last Name	First Name	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger
4	Nilsen	Johan	Bakken 2	Stavanger
5	Tjessem	Jakob

Now we want to update the person "Tjessem, Jakob" in the "PERSONS" table.

We use the following SQL statement:

UPDATE Persons
SET Address='Nissestien 67', City='Sandnes'
WHERE LastName='Tjessem' AND FirstName='Jakob'

The "Persons" table will now look like this

P_Id	Last Name	First Name	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger
4	Nilsen	Johan	Bakken 2	Stavanger
5	Tjessem	Jakob	Nissestien 67	Sandnes

 

SQL UPDATE Warning

Be careful when updating records. If we had omitted the WHERE clause in the example above, like this:

UPDATE Persons
SET Address='Nissestien 67', City='Sandnes'

The "PERSONS" table would have looked like this:
 

P_Id	Last Name	First Name	Address	City
1	Hansen	Ola	Nissestien 67	Sandnes
2	Svendson	Tove	Nissestien 67	Sandnes
3	Pettersen	Kari	Nissestien 67	Sandnes
4	Nilsen	Johan	Nissestien 67	Sandnes
5	Tjessem	Jakob	Nissestien 67	Sandnes

Example 1 :
 

update emp

set sal = sal*2.25

where deptno = 30

Example 2 :

update emp

 set sal = sal*2.25

 where empno in ( select empno from emp_1 );