Update Statement in Oracle

Update statement in Oracle is used to update existing records of the table.

You can modify one or more than one columns using an update statement.

The update statement is classified as DML (Data Manipulation Language) statement by Oracle.

UPDATE Statement


UPDATE [schema_name.]table_name
   SET column_1 = value_1 | expression_1,
       column_2 = (SELECT value_2 | expression_2 
                     FROM table_name2 
                    WHERE conditions),
       column_n = value_n | expression_n
 WHERE condition_1
   AND condition_2
   AND subquery_condition_3
   AND condition_n;
Parameters Explanation

schema_name (optional)

  • Name of the schema to which the table belongs


  • Table name to update


  • A table used in sub-query

column_1, column_2…column_n

  • Columns of the table

condition_1, condition_2…condition_n

  • Conditions to filter the data


  • Sub-query conditions

value_1 | expression_1, value_2 | expression_2, value_3 | expression_3, value_n | expression_n

  • Value to update in columns


  • Direct update
UPDATE employees
   SET salary = salary + 10%, dept_no = 10
 WHERE dept_id = 20;

In this example, employees belonging to department 20 will be moved to department 10 with a 10% hike in their salary.

  • Update using another table
UPDATE employees
SET dept_no = (SELECT departments.dept_no 
                 FROM departments 
                WHERE departments.dept_name = ‘IT’)
WHERE dept_no = 20;

Here, department number of employees belonging to department 20 is changed to IT using departments table.

  • Update using sub-query
UPDATE employees
SET dept_no = 10
WHERE dept_no = (SELECT dept_no 
                   FROM departments 
                  WHERE dept_name = ‘HR’);

In this example, the HR department number updates to 10.

I hope you like the page and find it useful. We highly appreciate comments and feedback.

You can also Subscribe here to stay updated on latest posts of OracleMine.com.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.