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

Syntax

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

  • Table name to update

table_name2

  • 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

subquery_condition_3

  • Sub-query conditions

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

  • Value to update in columns

Example

  • 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.

Hi I am Paras.

Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!

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.