How to INSERT If Row Does Not Exist in PL/SQL

The database management system (DBMS) stores, processes, and manipulates data in a database. All data in a database can be stored using the Insert command or updated using the Update command. These two data manipulation language (DML) commands play a key role in adding and maintaining the data. Sometimes we may need to use these commands in a single SQL Query or a sequence of SQL commands in a single process. So in PL/SQL, these two Insert and Update commands combined in a single process are called UPSERT.

In this article, we will discuss in detail examples of the UPSERT concept of Update data if it already exists or Insert if it does not exist, in PL/SQL.

UPSERT Operations in PL/SQL

An UPSERT is a data manipulation operation that combines two DML commands, UPDATE and INSERT. So UPSERT can be defined as a way to update data if it already exists or Insert a Row of Data if it does not exist already. There are different ways we can perform the UPSERT in PL/SQL. A few common methods can be as below:

For example the different methods of Insert and Update, below the sample database and the initial data:

create table EMPLOYEES (  
  empno             number,  
  name              varchar2(50) not null,  
  jobrole           varchar2(50),
  technology         varchar2(50),
  hiredate          date,  
  salary            number(7,2),  
  constraint pk_employees primary key (empno)  
);

Insert into Employees (empno,name,jobrole,technology,hiredate,salary) 
Values (1,'Nirmal Singh','Developer','.NET','',25000);
Insert into Employees (empno,name,jobrole,technology,hiredate,salary) 
Values (2,'Mohan Raj','Senior Developer','.NET','',35000);
Insert into Employees (empno,name,jobrole,technology,hiredate,salary) 
Values (3, 'Manoharan M','Senior Developer','.NET','',35000);
Insert into Employees (empno,name,jobrole,technology,hiredate,salary) 
Values (4, 'Jenson Durai','Associate Developer','JAVA','',30000);
Insert into Employees (empno,name,jobrole,technology,hiredate,salary) 
Values (5, 'Madan Mohan','Project Manager','MBA','',75000);

1. Update … SQL%rowcount … Insert method

Example 1: Update

In the below example, we can that the where clause in update statement checks for empno 5 if it exists. Since this already exists, the existing data is updated to this employee data in table with the data provided to update. So the SQL%rowcount value will return 1 and the check for SQL%rowcount=0 will be false and the Insert statement inside the IF..Then bloc will not be executed.

DECLARE
 icount INT;
BEGIN
  Update Employees set jobrole='Sr. Project Manager', technology='MBA,PMPO',Salary=95000 where empno=5;
  if SQL%rowcount = 0 then
      Insert into Employees (empno,name,jobrole,technology,hiredate,salary)
      Values (5, 'Madan Mohan','Sr. Project Manager','MBA,PMPO','',95000);
  end if; 
END;

Example 2: Insert

The example uses the same method as given in example 1 but now the Insert statement will be executed since the Empno 6 does not exist and the SQL%rowcount returns 0 and check for SQL%rowcount=0 returns True and the Insert statement inside IF…Then bloc will be executed.

DECLARE
 icount INT;
BEGIN
  Update Employees set technology='.NET, ASP.NET' where empno=6;
  if SQL%rowcount = 0 then
      Insert into Employees (empno,name,jobrole,technology,hiredate,salary)
      Values (6,'Kovil Pillai',' Senior Developer','.NET, Java','',55000);
  end if; 
END;

If we run a Select query of the Employees table, below output can be seen

Output:

Data Updated and Inserted using SQL%rowcount check method

In the output above we can see that data for empno 5 is updated with new Tecnhnology and Salary information as this exists already. And also a new employee record with empno 6 is added to the table as per the example 1 and 2 code above.

2. Select Count … If…Else…

Example 1: Update

DECLARE
  n_count number;
BEGIN
  Select count(1) into n_count from Employees Where empno = 3;
  if n_count > 0 then
          Update Employees set technology='.NET, ASP.NET,AZURE,React Js' where empno=3;
  else
      Insert into Employees (empno,name,jobrole,technology,hiredate,salary)
        Values (3,'Manoharan M','Senior Developer','.NET','',35000);
  end if;
END;

When we run the above code block, the record with empno 3 will be updated with new information in the Technology column as the iCount will return value 1 and n_count>0 will be True in the If condition and the Update statement under the IF block will be executed..

Example 2: Insert

DECLARE
  n_count number;
BEGIN
  Select count(1) into n_count from Employees Where empno = 7;
  if n_count > 0 then
          Update Employees set technology='.NET, ASP.NET,AZURE,React Js' where empno=7;
  else
      Insert into Employees (empno,name,jobrole,technology,hiredate,salary)
         Values (7,'Sangeetha M','Senior Developer','.NET,ASP.NET,AZURE,,React Js','',55000);
  end if;
END;

When we run the above query, since there is no data for empno 7 in the table the n_count variable will return 0 value and based on the condition as there is 0 value, the if condition will return False and the Else part of the If statement above will be executed to Insert new data to the employees table.

Output:

Insert and Update using Count…If..Else method

3. MERGE method

The merge method is used with 2 tables to Upate or Insert data between tables.

We will create a backup table for the emloyees table.

create table EMPLOYEES_Backup (  
  empno             number,  
  name              varchar2(50) not null,  
  jobrole           varchar2(50),
  technology         varchar2(50),
  hiredate          date,  
  salary            number(7,2),  
  constraint pk_employeesBK primary key (empno)  
);

Example 1: Insert

MERGE INTO EMPLOYEES_BackUp bk
USING EMPLOYEES lv
ON (bk.empno = lv.empno)
WHEN NOT MATCHED THEN
  Insert (bk.empno,bk.name,bk.jobrole,bk.technology,bk.hiredate,bk.salary)
  VALUES (lv.empno, lv.name,lv.jobrole,lv.technology,lv.hiredate, lv.salary)
WHEN MATCHED THEN
  Update SET bk.technology=lv.technology,bk.jobrole=lv.jobrole;

The above Merge command will Insert all data from Employees to Employees_backUp table as there will be no data in the EMPLOYEES_BackUp table initially.

Example 2: Update and Insert

In the Employees table below update and Insert are done to explain the Update and Insert Merge method:

Update Employees set JobRole='Technical Manager', Salary=6000  where empno=7;
Insert into Employees (empno,name,jobrole,technology,hiredate,salary)  Values (8,'Priya Mohan','HR Manager','MBA','',35000);

When we run the Merge command again as below, the updated and Inserted data are added to the Employee_Backup table from Employees table in a single execution.

MERGE INTO EMPLOYEES_BackUp bk
USING EMPLOYEES lv
ON (bk.empno = lv.empno)
WHEN NOT MATCHED THEN
  Insert (bk.empno,bk.name,bk.jobrole,bk.technology,bk.hiredate,bk.salary)
  VALUES (lv.empno, lv.name,lv.jobrole,lv.technology,lv.hiredate, lv.salary)
WHEN MATCHED THEN
  Update SET bk.technology=lv.technology,bk.jobrole=lv.jobrole;

Output:

If we look at the Employees Backup table you can see the below result of data updated and inserted in the EMPLOYEES_BackUp table as per the above merge command:

Merge command example result

Conclusion

In this article we have discussed about the UPSERT concept of Updating and Inserting data in a database table in PL/SQL. Three methods of update if exists else Insert operation was explained in these 3 methods, with MERGE option of data update and/or Insert in a single execution. From these methods any method can be used based on need and need of the data being updated or inserted.