DML

DML (Data Manipulation Language) is the part of SQL that works with the data inside tables: reading it, adding it, changing it, and deleting it.

What is DML

• DML touches rows, not structures. DDL builds the table, DML fills it.
• The four DML statements are SELECT, INSERT, UPDATE, and DELETE.
• DML changes are not permanent until COMMIT. ROLLBACK throws them away.
• Each of these statements has its own page later in this module with full syntax and examples.

The four DML statements

• SELECT :- Reads rows from one or more tables. Never changes data.
• INSERT :- Adds new rows to a table, either one row of values or rows from a SELECT.
• UPDATE :- Changes values in existing rows. Without WHERE, it changes every row.
• DELETE :- Removes rows from a table. Without WHERE, it removes every row.

DML and transactions

• INSERT, UPDATE, and DELETE lock the rows they touch until COMMIT or ROLLBACK.
• Keep the work between commits small so locks are held for a short time.
• Always check SQLCODE after each DML statement before deciding to commit.

Example

• One row through the full DML life cycle - insert it, change it, read it, delete it:-