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


Have a question on this subject?

Ask questions to our expert community members and clear your doubts. Asking question or engaging in technical discussion is both easy and rewarding.

Are you on Twitter?

Start following us. This way we will always keep you updated with what's happening in Data Analytics community. We won't spam you. Promise.

  • Introduction to ANSI SQL

    We human beings communicate with each other with the help of language. Similiarly, SQL stands for Structured Query Language is the language that a database understands, and we will communicate to the database using SQL. SQL is a 4TH generation...

  • SQL- Data Manipulation Language

    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.

  • DW Implementation Using EDB Postgres

    PostgreSQL is an advanced open source relational database management system. EDB develops and integrates performance, security, and manageability enhancements into PostgreSQL to support enterprise-class workloads for its database, EDB Postgres...

  • SQL- Data Query Language

    Structured Query Language (SQL) can be further subdivided in multiple parts based on its functionality. Data Query Language (DQL) mainly deals with SQL SELECT statement for retrieving data from a database

  • Top 20 SQL Interview Questions with Answers

    SQL is a language for accessing and manipulating database standardized by ANSI. To be successful with database-centric applications (which includes most of the applications Data Warehousing domain), one must be strong enough in SQL. In this...

  • Complex Xml Generation from Database

    In this article we will generate complex XML tags/file directly from Database tables using simple SQL SELECT statement. Almost every database vendors provides support for XML read and write functionality. So let us exploit the available database...

  • SQL- Data Definition Language

    The Data Definition Language (DDL) manages database table and index structure. The most basic commands of DDL are the CREATE, ALTER, RENAME, DROP and TRUNCATE statements.