syntax and example of update statement in oracle sql

The UPDATE statement in Oracle SQL / PLSQL allows us to update record(s)in a table.

Syntax for the UPDATE statement in Oracle SQL / PLSQL is:

UPDATE table_name
SET column_name = expression
WHERE conditions;

Let’s take an example for understanding:
Suppose have a table named ‘employee’ in the database as shown below.

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Scenario 1:

Here we can see that employee_id = 104 does not have any ‘commission’ associated with him, and we want the value of ‘commission’ to be 5.
The same can be achieved using the UPDATE statement as follows:

UPDATE employee
SET    commission  = 5
WHERE  employee_id = 104;

The above statement will update the record of the table where employee_id = 104 and put a value in the ‘commission’ column as 5.
Now if we query the ‘employee’ table as;

SELECT *
FROM employee;

We will get the following result:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support	5
105	Emp E	32000	Sales	10

Here we can see that a record with employee_id = 104 has been successfully updated in to the ‘employee’ table.

Scenario 2:

Updating more than one column at a time in an already existing table:
Let’s assume that we have a new table namely ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Here we can see that employee_id =104 does not have any ‘commission’ associated with him, and we want the value of ‘commission’ to be 5 and the department to be ‘IT’.

We can achieve the same as:

UPDATE employee
SET    commission  = 5
       ,department = 'IT'
WHERE  employee_id = 104;

Once we have run the above code we can query the ‘employee’ table and the following will be the result:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	IT	5
105	Emp E	32000	Sales	10

Here we have successfully updated ‘employee’ table in one shot.

Scenario 3:

Updating records of a table from records of another table:
Let’s assume that we have a new table namely ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales
102	Emp B	20000	IT
103	Emp C	28000	IT
104	Emp D	30000	Support
105	Emp E	32000	Sales

Here we can see that ‘commission’column does not have any value for any employee.
Also, we have a table named ‘comm’ which looks as shown below:

Emp_ID	Commission_Percent
101	10
102	20
103	20
104	15
105	10

We want the ‘employee’ table to be updated with the commission percentage data from the ‘comm’ table.

We can achieve the same as:

UPDATE employee
SET employee.commission  = (SELECT comm.commiossion_percent
                            FROM comm
                            WHERE employee.employee_id = comm.emp_id);

Once we have run the above code we can query the ‘employee’ table and following will be the result:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support	15
105	Emp E	32000	Sales	10

Here we have successfully updated ‘employee’ table from the ‘comm’ table.