Data Manipulation Language changes what is in your tables. Run INSERT, UPDATE and DELETE against a live table, watch every affected row light up, and use a transaction to undo the damage.
Four verbs do almost all the work in day-to-day SQL. Two of them can ruin your afternoon if you forget a WHERE clause.
Data Manipulation Language covers the statements that read and change rows: INSERT, UPDATE, DELETE and SELECT. Where DDL defines the container, DML fills and reshapes its contents.
UPDATE employees SET salary = 50000; -- every employee. All of them.
DELETE FROM orders; -- every order.Both are valid SQL, both run without complaint, and both are the reason database people flinch at typing UPDATE into a production console.
Three defences, in order of reliability:
SELECT first. Run SELECT * FROM employees WHERE dept_id = 3 and look at what comes back. Then change the word SELECT * to UPDATE ... SET, keeping the same WHERE.BEGIN, run the statement, check the reported row count, then COMMIT or ROLLBACK. If it says "4,912 rows affected" and you expected 12, roll back.sql_safe_updates refuses an UPDATE or DELETE without a key-based WHERE.The row count is the signal that catches this. Get into the habit of predicting it before running and comparing afterwards.
Unlike DDL, DML is transactional. Wrap your changes in a transaction and nothing is permanent until you say so:
BEGIN; UPDATE employees SET salary = salary * 1.1 WHERE dept = 'Engineering'; -- check the result ROLLBACK; -- or COMMIT;
Press BEGIN in this lab, delete some rows, then press ROLLBACK — they all come back. This is the single most valuable habit when running a statement you are not completely sure about.
Transactions also provide atomicity: if you debit one account and credit another, either both happen or neither does. A crash between the two statements cannot leave money missing.
DDL defines the containers; DML fills, changes and empties them.
-- INSERT: add rows
INSERT INTO employees (name, email, salary)
VALUES ('Alice', 'alice@example.com', 75000),
('Bob', 'bob@example.com', 62000);
-- UPDATE: change existing rows
UPDATE employees SET salary = salary * 1.05 WHERE dept_id = 3;
-- DELETE: remove rows
DELETE FROM employees WHERE hired_on < '2015-01-01';
-- SELECT: read rows (the one you run a thousand times a day)
SELECT name, salary FROM employees WHERE salary > 70000;Two habits worth adopting immediately. Always list the columns in an INSERT — INSERT INTO employees VALUES (...) breaks the moment someone adds a column. And insert many rows in one statement rather than one statement per row; the difference on a few thousand rows is often a hundredfold, because each statement carries its own round trip and transaction overhead.
"Insert this row, or update it if it already exists" is such a common need that every engine has a form of it, and doing it with a SELECT followed by an INSERT is a race condition waiting to happen.
-- PostgreSQL and SQLite
INSERT INTO inventory (sku, quantity)
VALUES ('ABC-1', 10)
ON CONFLICT (sku) DO UPDATE SET quantity = inventory.quantity + EXCLUDED.quantity;
-- MySQL
INSERT INTO inventory (sku, quantity) VALUES ('ABC-1', 10)
ON DUPLICATE KEY UPDATE quantity = quantity + VALUES(quantity);
-- Standard SQL / SQL Server / Oracle
MERGE INTO inventory AS t
USING (SELECT 'ABC-1' AS sku, 10 AS qty) AS s ON t.sku = s.sku
WHEN MATCHED THEN UPDATE SET quantity = t.quantity + s.qty
WHEN NOT MATCHED THEN INSERT (sku, quantity) VALUES (s.sku, s.qty);The conflict target must be backed by a unique constraint or index — the engine needs a defined way to detect the collision.
RETURNING (PostgreSQL, and now several others) is the natural companion: it hands back the affected rows, so you can get the generated id without a second query.
INSERT INTO employees (name) VALUES ('Carol') RETURNING id, hired_on;INSERT, UPDATE and DELETE are the three statements that change data, and two of them take a WHERE clause that is optional in the grammar and mandatory in practice. Everything here runs against a copy you can reset.
DML is where data actually changes, and the WHERE clause is what stands between a targeted fix and a table-wide accident. Preview with SELECT, wrap risky work in a transaction, and let the constraints you declared in DDL catch what you miss.
Changing rows based on data elsewhere is routine, and the syntax is one of the least portable corners of SQL.
-- PostgreSQL
UPDATE employees e
SET dept_name = d.name
FROM departments d
WHERE d.id = e.dept_id;
-- MySQL
UPDATE employees e
JOIN departments d ON d.id = e.dept_id
SET e.dept_name = d.name;
-- Portable, if slower
UPDATE employees e
SET dept_name = (SELECT name FROM departments d WHERE d.id = e.dept_id);The portable version has a trap: rows with no matching department get NULL rather than being left alone. Add WHERE EXISTS (...) if that is not what you want.
A DELETE touching millions of rows holds locks, generates enormous undo or WAL volume, and can block everything else for minutes. Batch it instead:
-- repeat until zero rows are affected
DELETE FROM events
WHERE id IN (SELECT id FROM events WHERE created_at < '2025-01-01' LIMIT 10000);Each batch is a short transaction, other queries get a chance to run between them, and an interruption leaves the job resumable rather than rolled back.
For deleting most of a very large table, an even better pattern is to copy the rows you are keeping into a new table, then swap the names. And for data with a natural time boundary, partitioning turns "delete last year" into "drop a partition", which is instant.
Does UPDATE rewrite the whole row? In PostgreSQL, yes — an update writes a new row version and marks the old one dead, which is why heavily updated tables need vacuuming. In MySQL's InnoDB, only the changed columns and the undo record.
Why did my INSERT succeed but the data is gone? Autocommit was off and the transaction was never committed, or a rollback happened later in the session.
Can I INSERT from a SELECT? Yes — INSERT INTO archive SELECT * FROM orders WHERE completed is the standard way to copy rows between tables.
How do I get the id of a row I just inserted? RETURNING id where supported, otherwise the driver's last-insert-id function. Never re-query by the values you inserted; that is racy.
Is TRUNCATE a DML statement? It is usually classified as DDL, which is why it auto-commits in some engines and does not fire triggers. Treat it as a structural operation.
What happens if an INSERT violates a constraint? The statement fails and, inside a transaction, the whole transaction is typically marked as aborted — in PostgreSQL you must roll back or use a savepoint before continuing.
INSERT, UPDATE, DELETE and SELECT are the statements that move data.INSERT, and insert in batches rather than row by row.WHERE is the classic disaster — write the SELECT first, and use a transaction.ON CONFLICT / ON DUPLICATE KEY / MERGE), never in read-then-write application code.About to run: Now do it inside a transaction. Before it does — what happens to the readout?
Committing to an answer first is the point — the reveal runs the experiment on the visualisation above and reads the real value back, so nothing here is scripted.
Say the answer out loud before you reveal it — recalling it is what makes it stick, and rereading it is not.
Without scrolling back — what is the one-line takeaway from this module?
DML is where data actually changes, and the WHERE clause is what stands between a targeted fix and a table-wide accident. Preview with SELECT, wrap risky work in a transaction, and let the constraints you declared in DDL catch what you miss.
What does this module say about “The idea in brief”?
Data Manipulation Language covers the statements that read and change rows: INSERT , UPDATE , DELETE and SELECT . Where DDL defines the container, DML fills and reshapes its contents.
What does this module say about “The Four Statements”?
Both are valid SQL, both run without complaint, and both are the reason database people flinch at typing UPDATE into a production console.
Data Manipulation Language changes what is in your tables. Run INSERT, UPDATE and DELETE against a live table, watch every affected row light up, and use a transaction to undo the damage.