UPDATE Statement in Oracle SQL – PLSQL

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.

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:

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:

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:

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:

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:

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:

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