Data Manipulation Language (DML) is the name given to the SQL statements used to manage data in a relational database. DML statements include INSERT, UPDATE, DELETE and MERGE. Each of these statements manipulates data in tables.
The SELECT statement is generally grouped with the other four statements under the DML class of SQL operations. However, SELECT statements do not add, alter, or remove rows from database tables – so no manipulation is involved. However, if the SELECT command is not included with DML, then it has no place to be. It certainly does not fit in with Data Definition Language (DDL), Data Control Language (DCL), or Transaction Control Language (TCL). Just be aware that when reference is made to DML statements, the context may not include SELECT operations.
Data manipulation language statements are utilized to manage data in existing schema objects. DML statements do not modify information in the data dictionary and do not implicitly commit the current transaction. The most commonly identified DML commands are:
INSERT – Used to populate data in tables. It is possible to insert one row into one table, one row into multiple tables, multiple rows into one table, or multiple rows into multiple tables.
UPDATE – Used to alter data that has already been inserted into a database table. An UPDATE can affect a single row or multiple rows, and a single column or multiple columns. The WHERE clause will determine which rows in the table are altered. When executed with no WHERE clause, it will update all rows in the target table. A single UPDATE statement can only act on one table.
DELETE – Used to remove previously inserted rows from a table. The command can remove a single row or multiple rows from a table. When executed with no WHERE clause, it will remove all rows from the target table. It is not possible to delete individual columns – the entire row is deleted or it is not.
MERGE – Used for hybrid DML operations. The MERGE can insert, update and delete rows in a table all in a single statement. There is no operation that a MERGE can perform that could not be performed by a combination of INSERT, UPDATE and DELETE.
