Oracle UPDATE Command

The Oracle UPDATE statement processes one or more rows in a table and sets one or more columns to the values you specify.

Chooses which rows to update in the database table. Only rows that meet the where condition are updated. If you omit this clause, all rows in the table are updated.

1. Modify multiple rows with a single UPDATE statement

SQL> UPDATE Employee
  2  SET City ='L.A.'
  3  WHERE City = 'New York';

2 rows updated.

2. Without the WHERE clause, this UPDATE affects all rows in the table

SQL> UPDATE Employee
  2  SET Salary = Salary * 1.1;

8 rows updated.

3. Update with where condition

SQL> update employee 
     set salary = salary * 1.15 
     where id = '02';

1 row updated.

4. Change value case with update statement

SQL> UPDATE product
  2  SET    product_name = 'product Number'
  3  WHERE  product_name = 'Product Number';

1 row updated.

5. Update records in one table based on values in another table by using Subquery

SQL> UPDATE emp
  2      SET ename = ( SELECT dname
  3                    FROM dept
  4                    WHERE emp.deptno = dept.deptno)
  5      WHERE EXISTS
  6        ( SELECT dname
  7                    FROM dept
  8                    WHERE emp.deptno = dept.deptno);

6. Writing an UPDATE Statement Containing a Subquery

Set the new column value equal to the result returned by a single row subquery

SQL> UPDATE employee
  2  SET salary =
  3    (SELECT AVG(salary)
  4     FROM employee);

8 rows updated.

7. You can update a column and set it back to the default using the DEFAULT keyword in an UPDATE statement

SQL> UPDATE order_status
  2  SET status = DEFAULT
  3  WHERE order_status_id = 2;

1 row updated.

8. Use arithmetic operators and functions in the SET clause to modify data

SQL> UPDATE Employee
  2  SET Salary = Salary + 50
  3  WHERE ID = '01';

1 row updated.

9. An example of using a function in the SET clause

SQL> UPDATE Employee
  2  SET First_Name = UPPER(First_Name)
  3  WHERE ID = '01';

1 row updated.

10. Use TO_DATE('December 31, 2002, 11:30 P.M.','Month dd, YYYY, HH:MI P.M.') in set statement

SQL> UPDATE product
  2  SET    last_stock_date = TO_DATE('December 31, 2002, 11:30 P.M.',
  3                                   'Month dd, YYYY, HH:MI P.M.')
  4  WHERE  product_name LIKE '%Zinc%';

0 rows updated.

11. Change multiple rows and multiple columns in the same UPDATE statement

SQL> UPDATE employee
  2  SET
  3    salary = salary * 1.20,
  4    first_name = LOWER(first_name)
  5  WHERE
  6    salary >= 4000;

4 rows updated.

12.Update all records without condition

SQL>  update employee set city = 'BOSTON';

8 rows updated.