DML statements

DML statements access and manipulate data in existing schema objects.

DDL statements change database structures, whereas DML statements query or modify data in schema objects. For example, the ALTER TABLE statement changes a table schema, whereas the INSERT statement inserts one or more rows into a table.

DML statements are the most frequently used SQL statements. You can use them to perform the following operations:

• Use the SELECT statement to query data in one or more tables or views.

• Use the INSERT statement to insert values or query results into a table or view.

• Use the UPDATE statement to change column values in an existing row of a table or view.

• Use the MERGE statement to update or insert a row into a table or a view based on the specified conditions.

• Use the DELETE statement to remove rows from a table.

• Use the EXPLAIN statement to view the execution plan for an SQL statement.

The following example uses DML statements to query the customer table, insert a row into the table, update the row, and then delete the row.

SELECT * FROM customer;
INSERT INTO customer VALUES(1234, 'Tom', 'Jacy');
UPDATE customer SET cust_name = 'Tomy' WHERE cust_id = 11;
DELETE customer WHERE cust_id = 11;

A transaction is a set of DML statements that make up a logical work unit. Unlike DDL statements, DML statements do not cause an implicit commit.

The following example helps you better understand what a transaction is: If money is transferred from Account A to Account B, three operations are involved. First, the balance in Account A decreases. Second, the balance in Account B increases. Third, the transfer is recorded in the transfer history tables of the accounts. These three operations can be collectively called a transaction.