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.

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.


Tagged , , , , , , , , , , , . Bookmark the permalink.