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 [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;
- Name of the schema to which the table belongs
- Table name to update
- A table used in sub-query
- Columns of the table
- 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.
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 firstname.lastname@example.org. Again I appreciate your visit. Hope to see you again and again!