DML Commands in SQL: INSERT, UPDATE, DELETE, and MERGE (with Examples)

Relational databases separate SQL instructions into functional sub-languages: DDL defines structure, DCL manages permissions, TCL controls transactions, and dml commands in sql manipulate the actual data records stored inside tables.

Whenever an e-commerce checkout registers an order, a user updates their profile address, or a data engineering pipeline stages nightly analytics tables, DML statements are executing in the background. For data analysts and analytics engineers, knowing how to write robust DML statements is essential for creating test fixtures, staging transformed data mart tables, and backfilling missing historical metrics.

In this tutorial, you will master the syntax and execution mechanics of the primary dml commands in sql, study defensive programming practices like explicit transactions (BEGIN / ROLLBACK) to prevent accidental table wipes, and compare DML operations against DDL commands.

To test your knowledge against real-world scenario questions, visit our SQL Interview Questions Guide and explore our complete SQL Cheat Sheet.

DML Commands in SQL: Syntax and Core Statements

The four primary DML commands are INSERT, SELECT, UPDATE, and DELETE, supplemented by modern MERGE / UPSERT statements.

-- The Core DML Family:
1. INSERT   -- Add new rows to a table
2. SELECT   -- Retrieve and inspect data rows
3. UPDATE   -- Modify column values in existing rows
4. DELETE   -- Remove specific rows from a table
5. MERGE    -- Conditional insert-or-update (UPSERT)

1. INSERT INTO: Adding New Data Records

The INSERT statement appends one or more rows into an existing table structure.

Single-Row and Multi-Row Inserts

-- Single-row insert specifying columns explicitly
INSERT INTO customers (customer_id, first_name, email, city, status)
VALUES (101, 'Priya', 'priya@example.com', 'Bengaluru', 'active');
 
-- Multi-row batch insert (highly performant)
INSERT INTO customers (customer_id, first_name, email, city, status)
VALUES 
    (102, 'Rohan', 'rohan@example.com', 'Mumbai', 'active'),
    (103, 'Arjun', 'arjun@example.com', 'Delhi', 'pending'),
    (104, 'Meera', 'meera@example.com', 'Chennai', 'active');

INSERT INTO ... SELECT (ETL Staging)

Analysts frequently copy transformed records from a raw staging table into an analytics table:

INSERT INTO daily_revenue_summary (report_date, total_revenue, order_count)
SELECT 
    order_date::DATE,
    SUM(amount) AS total_revenue,
    COUNT(order_id) AS order_count
FROM raw_orders
WHERE status = 'Completed'
GROUP BY order_date::DATE;

2. UPDATE: Modifying Existing Records

The UPDATE statement alters values in one or more columns for rows matching a predicate.

UPDATE customers
SET status = 'premium',
    loyalty_points = loyalty_points + 500,
    updated_at = CURRENT_TIMESTAMP
WHERE customer_id = 101;

Updating with Joins (Cross-Table Updates)

In PostgreSQL, you can update a table based on values from another table using the FROM clause:

UPDATE employees e
SET salary = e.salary * 1.10
FROM departments d
WHERE e.department_id = d.department_id
  AND d.department_name = 'Engineering';

3. DELETE: Removing Specific Records

The DELETE command deletes rows from a table while preserving the table schema, indexes, and constraints.

DELETE FROM active_carts
WHERE updated_at < CURRENT_TIMESTAMP - INTERVAL '30 days';

To delete duplicate rows while retaining the primary copy, see our dedicated guide on how to delete duplicate records in SQL.

4. MERGE and UPSERT (Insert or Update)

In real-world data pipelines, you often do not know whether a record already exists. Attempting to insert a duplicate primary key causes a constraint violation error. An UPSERT handles this cleanly.

PostgreSQL Syntax (ON CONFLICT)

INSERT INTO product_inventory (sku, quantity, last_restocked)
VALUES ('SKU-108', 50, CURRENT_DATE)
ON CONFLICT (sku) 
DO UPDATE SET 
    quantity = product_inventory.quantity + EXCLUDED.quantity,
    last_restocked = EXCLUDED.last_restocked;

ANSI Standard SQL MERGE (Snowflake, BigQuery, SQL Server)

MERGE INTO target_customers t
USING stage_customers s ON t.customer_id = s.customer_id
WHEN MATCHED THEN
    UPDATE SET t.email = s.email, t.updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
    INSERT (customer_id, email, created_at)
    VALUES (s.customer_id, s.email, CURRENT_TIMESTAMP);

Advanced DML Commands in SQL: RETURNING Clauses and Transaction Control

In high-throughput transactional backends and analytics ingestion pipelines, basic DML statements are enhanced with atomic result returns and explicit transaction boundaries. Explore more patterns in our SQL Tutorials hub.

The RETURNING Clause in PostgreSQL and Modern SQL

Historically, after executing an INSERT or UPDATE, an application had to make a second round-trip query to fetch the newly generated primary key or updated timestamp. The RETURNING clause returns modified values directly in the original statement execution:

-- Insert customer and return the system-generated ID immediately
INSERT INTO dim_customers (full_name, email, tier)
VALUES ('Vikram Rao', 'vikram@example.com', 'Gold')
RETURNING customer_id, created_at;
 
-- Archive and return deleted records in a single query
DELETE FROM staging_orders
WHERE order_date < '2026-01-01'
RETURNING order_id, customer_id, total_amount;

Transaction Control with DML: COMMIT, ROLLBACK, and SAVEPOINT

DML statements execute within transaction scopes. In production migrations or complex multi-table adjustments, using SAVEPOINT provides fine-grained error recovery:

BEGIN TRANSACTION;
 
-- DML Operation 1: Deduct inventory
UPDATE product_inventory
SET stock_quantity = stock_quantity - 2
WHERE product_id = 101;
 
SAVEPOINT inventory_updated;
 
-- DML Operation 2: Create order invoice
INSERT INTO order_invoices (order_id, customer_id, amount)
VALUES (9481, 402, 250.00);
 
-- If invoice fails due to duplicate key, rollback only to the savepoint:
-- ROLLBACK TO SAVEPOINT inventory_updated;
 
COMMIT;

Common Mistakes When Executing DML Commands

BEGIN;
DELETE FROM customers WHERE status = 'churned' AND created_at < '2024-01-01';
-- Inspect the change:
SELECT COUNT(*) FROM customers;
-- If correct, commit. If wrong, rollback immediately:
COMMIT; -- or ROLLBACK;

1. Violating Foreign Key Constraints

Attempting to DELETE a parent customer row whose customer_id is referenced by child orders in an orders table will trigger a foreign key violation error unless cascading deletes (ON DELETE CASCADE) are configured.

2. Transaction Log Bloat

Deleting millions of rows in a single monolithic DELETE FROM huge_table WHERE ... can fill database transaction logs and lock tables for hours. Senior analytics engineers batch deletions in chunks of 50,000 rows or create a new table with desired rows and perform an atomic table swap using DDL.

DML vs DDL vs DCL vs TCL

To explore table definition syntax, continue to our companion guide on DDL commands in SQL.

When Analysts Use DML Commands in Real Work

Data Mart Transformation Pipelines. Using INSERT INTO ... SELECT and MERGE to load dimensional fact tables and roll-ups during scheduled dbt or Airflow jobs.

Backfilling Missing Data. Correcting corrupted currency conversions or country codes across historical transaction partitions using targeted UPDATE statements.

Unit Testing and Seed Fixtures. Writing clean INSERT INTO scripts to seed test databases before executing automated regression queries.

For deeper practice with SQL commands, review our SQL Order of Execution Tutorial and practice on our Live SQL Sandbox.

DML Commands in SQL: Auditing, Change Data Capture, and Soft Deletes

In enterprise data warehouses and GDPR/CCPA-compliant architectures, executing physical DELETE statements is often prohibited:

1. The Soft Delete Pattern: Instead of removing rows from disk, apply an UPDATE command that flags records with a timestamp:

   sql

   Copy code

   UPDATE users 
   SET is_deleted = TRUE, deleted_at = CURRENT_TIMESTAMP 
   WHERE user_id = 492;

2. Audit Logging via Database Triggers: Attach trigger procedures that record before-and-after snapshots of every UPDATE and DELETE into a dedicated audit_log table, preserving provenance for financial compliance.
3. Change Data Capture (CDC): Tools like Debezium monitor database write-ahead logs to stream every DML mutation into Kafka, synchronizing transactional tables with downstream analytical data lakes without placing query load on production OLTP replicas.

Master more database concepts in our SQL Tutorials hub and practice live on Topfolio Practice.

Related SQL Tutorials

• What Is SQL? The Complete Beginner to Pro Guide
• SQL Joins Explained with Practical Examples
• SQL Window Functions Guide
• SQL Cheat Sheet for Analysts
• Explore All Guides in the SQL Tutorials Hub