6 Simple Methods for Updating the Statement in an Oracle Table

Based on our case and the available table data, Oracle updates can occur in a variety of ways. Here are six easy techniques to update Oracle table data in a timely manner.

Use the data table data provided to try it out for yourself.

Table Structure :

create table tst_employee(empId number(10), empName varchar(100), empAge number(10));
create table tst_department(empId number(10), empDept varchar(100));
insert into tst_employee values(101,'Raj',25);
insert into tst_employee values(102,'Stephen',25);
insert into tst_employee values(103,'Dev',25);
insert into tst_employee values(104,'Mahil',25);
insert into tst_employee values(105,'Amul',25);
insert into tst_department values(101,'CSE');
insert into tst_department values(102,'MACH');
insert into tst_department values(103,'ECE');
insert into tst_department values(104,'AERO');
insert into tst_department values(105,'MARINE');

Scenario 1 – Update a Table with data from another Table using EXISTS :

EXISTS is a check that must be done before doing any action. We use the EXISTS keyword in the update statement to see if the necessary data is being matched with secondary tables, and if it is, we make a change.

UPDATE TST_EMPLOYEE t1
   SET EMPAGE = 20
 WHERE EXISTS (
    SELECT 1
      FROM TST_EMPLOYEE t2
     WHERE t1.EMPID = t1.EMPID );

Scenario 2 – Update a Table with data using Oracle MERGE :

MERGE is one of the most efficient operations for modifying a table. The MERGE statement is being used here to update a table based on a condition match.

MERGE INTO tst_employee t1 USING
( SELECT EMPID FROM tst_employee
)t2 ON(t1.empid = t2.empid AND t1.empid = 103)
WHEN MATCHED THEN
  UPDATE SET t1.empage = 30;

Scenario 3 – Update a Table with data using SUB QUERY :

If we need to update a table’s data on the basis of a sub query, use the update statement below, which will scan the sub table based on the conditions.

UPDATE TST_EMPLOYEE t1
SET EMPAGE      = 40
WHERE t1.EMPID IN
  (SELECT t2.empid FROM tst_employee t2 WHERE t1.empid = '104'
  );

Scenario 4 – Update a Table with data using Oracle LOOP :

This is how we use the update statement in a method or block of statements inside a loop.

BEGIN
For i in (select empid from TST_EMPLOYEE ) 
LOOP
Update TST_DEPARTMENT t2 set empDept = empDept where t2.EMPID = i.empid;
END LOOP;
END;

Scenario 5 – Update a Table with data with Multiple Column Conditions :

This is one of the modifications, such as updating the table with a grouping of where criteria and applying it to a sub-table.

UPDATE TST_EMPLOYEE t1
SET EMPAGE              = 50
WHERE (t1.EMPID,'CSE') IN
  (SELECT t2.empid,t2.empDept FROM TST_DEPARTMENT t2
  )
AND t1.EMPID = 101;

Scenario 6 – Update a Table with data with CASE IF :

In the example above, the condition check is performed during the update using the case when…then..else..end;. When we need to update the table based on a condition, we’ll utilise this type of update.

UPDATE TST_EMPLOYEE t1
SET t1.EMPAGE =
  CASE
    WHEN (SELECT 1
      FROM TST_EMPLOYEE t2
      WHERE t2.EMPID = t1.empid
      AND t2.EMPID  IN (101,103)) = 1
    THEN 25
    ELSE 20
  END;