How to Modify Table Data, 4 Helpful SQL Queries

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

SQL: How to Use INSERT, UPDATE, DELETE and MERGE

INSERT Query

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

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

UPDATE SQL Query

update skew2
set col1 = col1*1;

This query will do 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 delete. 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 match, 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 insert with “APPEND” to delete bulk-rows. Here’s to read more on Merge SQL query.