SQL Queries: Insert, Update, Delete, and Merge

With SQL queries like INSERT, UPDATE, DELETE, and Merge, you can easily modify data in a Table.

Top SQLQeries

INSERT Query

insert /*+ append */ into kso.big_emp
select *
from hr.employees;

insert /*+ append_values */ into dual (dummy)
values (‘Y’); 

How to use both append and append-values.

UPDATE SQL Query

update skew2
set col1 = col1*1;

This query will do a mass update. Instead, use INSERT with “APPEND”. Hint to improve performance.

DELETE SQL Query

delete from kso.skew2 
where col1=1; 

This is also called mass deletion. If the rows are more, it takes more CPU time. If we want to delete all the rows, always use TRUNCATE.

MERGE SQL Query

It is introduced with 9i. The basic functionality is when matches, it will update. When not match it will insert.

MERGE INTO table_name
USING (subquery) ON (subquery.column = table.column)
WHEN MATCHED THEN UPDATE
WHEN NOT MATCHED THEN INSERT

Merge has its internal mechanism, it takes more CPU time. So always use an insert with “APPEND” to delete bulk rows. Here’s to read more on Merge SQL query.