SQL

SQL- Data Manipulation Language

Saurav Mitra

The Data Manipulation Language (DML) manages data operation like INSERT, UPDATE, DELETE to database tables. The most basic commands of DML are the INSERT, UPDATE, DELETE and MERGE statements.

Add data in a table

INSERT adds rows or tuples to an existing database table.

Syntax:

INSERT INTO table_name( column1, column2,... ) 
VALUES( value1, value2,... );

Syntax with an Example:

INSERT INTO dept( deptno, dname, loc) 
VALUES( 10, 'ACCOUNTING', 'NEW YORK' );

--Alternatively
INSERT INTO dept
VALUES( 20, 'RESEARCH', 'DALLAS' );

INSERT data from another table

Syntax:

INSERT INTO tablename( column 1, column2,... )
SELECT column1, column2,...
FROM another_tablename;

Note: Both the table definitions already present in database.

Syntax with an Example:

INSERT INTO dept_new
SELECT * FROM dept;

Modify Data in a Table

UPDATE modifies a set of data values of existing table rows.

Syntax:

UPDATE table_name SET
column1 = value1, column2 = value2 
WHERE column3 = value3;

Syntax with an Example:

UPDATE dept
SET dname= 'ACCOUNTS'
WHERE deptno = 10;

Remove data from a table

DELETE removes existing rows from a table.

Syntax:

DELETE FROM table_name
WHERE column1 = value1;

Syntax with an Example:

DELETE FROM dept
WHERE loc= 'DALLAS';

Add or modify data in a table

MERGE is used to combine the data of multiple tables. It combines the INSERT and UPDATE elements. It is defined in the SQL:2003 standard; prior to that, some databases provided similar functionality via different syntax, sometimes called "upsert".

Syntax:

MERGE INTO table1 USING table2 
ON ( condition ) 
WHEN MATCHED THEN UPDATE SET 
column1 = value1, column2 = value2,...
WHEN NOT MATCHED THEN INSERT 
( column1, column2,... ) 
VALUES( value1, value2,... );

Syntax with an Example:

MERGE INTO dept_dw tgt USING dept src 
on( src.deptno = tgt.deptno )
WHEN MATCHED THEN UPDATE SET
tgt.dname = src.dname, tgt.loc = trc.loc
WHEN NOT MATCHED THEN INSERT 
( deptno, dname, loc) VALUES 
( src.deptno, src.dname, src.loc);