SQL- Data Manipulation Language

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);