Mastering SQL DML: A Comprehensive Guide

Introduction to SQL DML

Structured Query Language (SQL) is the backbone of relational database management. Within SQL, statements are grouped into categories based on their purpose. Data Manipulation Language (DML) is the category dedicated to reading and modifying the actual data stored in database tables — as opposed to DDL (Data Definition Language), which defines structure, or DCL (Data Control Language), which manages permissions.

The four core DML operations are often referred to by the acronym CRUD:

• CREATE → INSERT (add new rows)
• READ → SELECT (query existing data)
• UPDATE → UPDATE (modify existing rows)
• DELETE → DELETE (remove rows)

A fifth operation, MERGE, combines INSERT, UPDATE, and DELETE into a single atomic statement and is supported in most modern databases. This blog explores each DML operation with syntax, real-world examples, and best practices.

Refer this for SQL execution order

1. SELECT — Reading Data

SELECT is the most frequently used SQL statement. It retrieves rows from one or more tables and can be combined with filtering, sorting, grouping, and joining to produce precisely the data you need.

Basic Syntax

SELECT column1, column2, ...
FROM   table_name
WHERE  condition
ORDER BY column1 ASC|DESC
LIMIT  n;

Examples

Select all columns

SELECT * FROM employees;

Filter with WHERE

SELECT first_name, last_name, salary
FROM   employees
WHERE  department = 'Engineering'
  AND  salary > 80000;

Aggregate functions

SELECT department,
       COUNT(*)        AS headcount,
       AVG(salary)     AS avg_salary,
       MAX(salary)     AS max_salary
FROM   employees
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY avg_salary DESC;

JOIN across tables

SELECT e.first_name, e.last_name, d.department_name
FROM   employees  e
JOIN   departments d ON e.department_id = d.id
WHERE  d.location = 'New York';

Key Clauses Reference

2. INSERT — Adding New Data

INSERT adds one or more new rows into a table. Every INSERT must respect the table’s constraints — primary keys must be unique, NOT NULL columns must have a value, and foreign key references must exist.

Basic Syntax

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Examples

Single row insert

INSERT INTO employees (first_name, last_name, department, salary, hire_date)
VALUES ('Priya', 'Sharma', 'Engineering', 95000, '2024-03-15');

Multiple rows at once

INSERT INTO products (name, category, price, stock)
VALUES
  ('Laptop Pro 15', 'Electronics', 1299.99, 50),
  ('Wireless Mouse', 'Accessories',   29.99, 200),
  ('USB-C Hub',      'Accessories',   49.99, 150);

Insert from a SELECT (INSERT … SELECT)

INSERT INTO archived_orders (order_id, customer_id, total, order_date)
SELECT order_id, customer_id, total, order_date
FROM   orders
WHERE  order_date < '2023-01-01';

Best Practices for INSERT

• Always specify column names explicitly — never rely on positional order.
• Use transactions when inserting multiple related rows to ensure atomicity.
• Validate data before inserting to avoid constraint violations.
• Use INSERT … ON CONFLICT (PostgreSQL) or INSERT IGNORE (MySQL) for upsert patterns.

3. UPDATE — Modifying Existing Data

UPDATE changes the values of one or more columns in existing rows. Always pair UPDATE with a WHERE clause — without it, every row in the table will be modified, which is rarely the intention.

Basic Syntax

UPDATE table_name
SET    column1 = value1,
       column2 = value2
WHERE  condition;

Examples

Update a single record

UPDATE employees
SET    salary = 105000,
       title  = 'Senior Engineer'
WHERE  employee_id = 42;

Conditional bulk update

UPDATE products
SET    price = price * 0.90   -- 10% discount
WHERE  category = 'Electronics'
  AND  stock > 100;

Update using a subquery

UPDATE orders
SET    status = 'Shipped'
WHERE  customer_id IN (
    SELECT id FROM customers
    WHERE  tier = 'Premium'
)
AND status = 'Processing';

Safety Checklist Before Running UPDATE

• Run a SELECT with the same WHERE clause first to preview affected rows.
• Wrap in a transaction so you can ROLLBACK if results are unexpected.
• Test on a staging environment before production.
• Confirm you have the right WHERE condition — missing it updates all rows!

4. DELETE — Removing Data

DELETE removes rows from a table permanently (unless inside a transaction). Like UPDATE, a missing WHERE clause will delete all rows. For large deletions, consider batching to avoid locking the table for extended periods.

Basic Syntax

DELETE FROM table_name
WHERE  condition;

Examples

Delete a specific record

DELETE FROM employees
WHERE  employee_id = 99;

Delete with a subquery

DELETE FROM order_items
WHERE  order_id IN (
    SELECT order_id FROM orders
    WHERE  status = 'Cancelled'
);

Batch delete (large tables)

-- Delete in chunks of 1000 to reduce lock time
DELETE FROM logs
WHERE  created_at < NOW() - INTERVAL '90 days'
LIMIT  1000;

DELETE vs TRUNCATE vs DROP

5. MERGE — The Upsert Operation

MERGE (also known as UPSERT) combines INSERT, UPDATE, and DELETE into one statement. It is ideal for synchronizing a source dataset into a target table — updating rows that already exist, inserting new ones, and optionally deleting rows that no longer appear in the source.

Basic Syntax (SQL Standard / SQL Server / Oracle)

MERGE INTO target_table AS target
USING source_table AS source
  ON  target.id = source.id
WHEN MATCHED THEN
    UPDATE SET target.name  = source.name,
               target.price = source.price
WHEN NOT MATCHED BY TARGET THEN
    INSERT (id, name, price)
    VALUES (source.id, source.name, source.price)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

PostgreSQL Equivalent (INSERT … ON CONFLICT)

INSERT INTO products (id, name, price)
VALUES (101, 'Keyboard', 79.99)
ON CONFLICT (id) DO UPDATE
  SET name  = EXCLUDED.name,
      price = EXCLUDED.price;

MySQL Equivalent (INSERT … ON DUPLICATE KEY UPDATE)

INSERT INTO products (id, name, price)
VALUES (101, 'Keyboard', 79.99)
ON DUPLICATE KEY UPDATE
  name  = VALUES(name),
  price = VALUES(price);

6. Transactions — Keeping DML Safe

A transaction groups multiple DML statements so they either all succeed or all fail together. This guarantees data consistency (the ACID properties) even in the event of an error or crash.

BEGIN;                          -- Start transaction
UPDATE accounts
SET    balance = balance - 500
WHERE  account_id = 1;          -- Debit
UPDATE accounts
SET    balance = balance + 500
WHERE  account_id = 2;          -- Credit
COMMIT;                         -- Confirm both changes
-- OR: ROLLBACK;               -- Undo everything if an error occurred

Use SAVEPOINT to create partial rollback points within a large transaction:

BEGIN;
  SAVEPOINT before_update;
  UPDATE orders SET status = 'Shipped' WHERE id = 500;
  -- Something went wrong?
  ROLLBACK TO SAVEPOINT before_update;
COMMIT;

7. DML Best Practices

Always Use WHERE on UPDATE and DELETE

Before executing any UPDATE or DELETE, run the equivalent SELECT with the same WHERE clause and verify the affected rows are exactly what you intended.

Wrap Multi-Step Operations in Transactions

Any business operation that spans multiple DML statements should be wrapped in a transaction. This ensures atomicity — partial updates won’t leave data in an inconsistent state.

Avoid SELECT * in Production Code

Selecting all columns wastes network bandwidth and can break application code when table schemas change. Always specify the columns you actually need.

Use Parameterized Queries to Prevent SQL Injection

-- ❌ Vulnerable to injection:
query = 'SELECT * FROM users WHERE name = ' + userInput
-- ✅ Safe parameterized form:
query = 'SELECT * FROM users WHERE name = ?'
execute(query, [userInput])

Index Columns Used in WHERE Clauses

DML operations that filter on unindexed columns perform full table scans. Ensure frequently filtered and joined columns are properly indexed to keep reads fast and lock durations short on writes.

Audit and Log Critical DML Operations

For sensitive tables (financial records, user data), implement audit logging — either via database triggers or application-level logging — to track who changed what and when.

Quick Reference Summary

Conclusion

SQL DML is the daily currency of backend and data engineering work. Mastering SELECT, INSERT, UPDATE, DELETE, and MERGE — and knowing when and how to use transactions — gives you the tools to interact with any relational database safely and efficiently.

The golden rules to remember:

• Test your WHERE clause with a SELECT before updating or deleting.
• Use transactions for multi-step operations.
• Specify column names explicitly in INSERT statements.
• Parameterize queries to prevent SQL injection.
• Index columns that appear frequently in WHERE and JOIN conditions.

Happy querying! 🚀