Home/Learn/SQL/INSERT, UPDATE & DELETE

INSERT, UPDATE & DELETE

Beginner

DML & Querying

INSERT adds rows, UPDATE modifies existing rows, and DELETE removes rows; each supports set-based operations and must be used carefully to avoid unintended mass mutations.

Overview

INSERT, UPDATE, and DELETE are the DML statements that change data. All three participate in transactions and trigger any defined BEFORE/AFTER triggers. INSERT supports multi-row syntax for batch inserts, which is far more efficient than looping single-row inserts. UPDATE without a WHERE clause updates every row in the table — a common production incident. DELETE without WHERE deletes all rows (but is slower than TRUNCATE because it is fully logged). UPSERT (INSERT ... ON CONFLICT DO UPDATE in PostgreSQL, INSERT ... ON DUPLICATE KEY UPDATE in MySQL) handles the insert-or-update pattern atomically. Always verify the affected row count after UPDATE/DELETE in application code.

INSERT Patterns

Single-row inserts inside a loop are a classic N+1 write anti-pattern. Multi-row inserts (batch insert) or COPY (PostgreSQL) are orders of magnitude faster for bulk data loading.

SQL — INSERT patterns, batch insert, UPSERT

-- Single-row insert
INSERT INTO products (sku, name, price, is_active)
VALUES ('ELEC-001', 'Wireless Headphones', 2999.00, TRUE);

-- Multi-row batch insert (single round-trip, much faster)
INSERT INTO products (sku, name, price, is_active) VALUES
    ('ELEC-002', 'USB-C Hub',         1499.00, TRUE),
    ('ELEC-003', 'Mechanical Keyboard', 3499.00, TRUE),
    ('ELEC-004', 'Webcam HD',          1999.00, FALSE);

-- INSERT ... SELECT: copy rows from another table
INSERT INTO products_archive
SELECT * FROM products WHERE is_active = FALSE;

-- UPSERT: insert or update on conflict (PostgreSQL)
INSERT INTO user_preferences (user_id, theme, notifications_enabled)
VALUES (42, 'dark', TRUE)
ON CONFLICT (user_id)
DO UPDATE SET
    theme                 = EXCLUDED.theme,
    notifications_enabled = EXCLUDED.notifications_enabled,
    updated_at            = NOW();

-- MySQL equivalent
INSERT INTO user_preferences (user_id, theme, notifications_enabled)
VALUES (42, 'dark', 1)
ON DUPLICATE KEY UPDATE
    theme                 = VALUES(theme),
    notifications_enabled = VALUES(notifications_enabled);

Safe UPDATE and DELETE

Always run a SELECT with the same WHERE clause before executing a destructive UPDATE or DELETE to verify the affected row set. Use RETURNING (PostgreSQL) to get the modified rows back in the same statement.

SQL — safe UPDATE, UPDATE with JOIN, DELETE, RETURNING

-- Safe UPDATE: always include WHERE, verify first
-- Step 1: verify rows that will be affected
SELECT id, status FROM orders WHERE user_id = 42 AND status = 'pending';

-- Step 2: update with same predicate
UPDATE orders
SET status = 'cancelled', updated_at = NOW()
WHERE user_id = 42 AND status = 'pending';

-- UPDATE with JOIN (PostgreSQL using FROM)
UPDATE orders o
SET status = 'suspended'
FROM users u
WHERE u.id = o.user_id
  AND u.status = 'banned';

-- MySQL UPDATE with JOIN syntax
UPDATE orders o
JOIN users u ON u.id = o.user_id
SET o.status = 'suspended'
WHERE u.status = 'banned';

-- RETURNING clause (PostgreSQL): get updated rows in one round-trip
UPDATE orders
SET status = 'shipped', shipped_at = NOW()
WHERE id = 1001
RETURNING id, status, shipped_at;

-- Safe DELETE with LIMIT (MySQL) — prevents accidentally deleting too many rows
DELETE FROM sessions WHERE expired_at < NOW() ORDER BY expired_at LIMIT 1000;

-- TRUNCATE vs DELETE: TRUNCATE is DDL, non-logged per-row, faster but not filterable
TRUNCATE TABLE sessions;         -- removes ALL rows, resets sequences, non-transactional in MySQL

Key Points to Remember

1Batch INSERT (multi-row VALUES) is far faster than single-row inserts in a loop.
2UPDATE and DELETE without WHERE affect ALL rows — always double-check the predicate.
3UPSERT (ON CONFLICT DO UPDATE / ON DUPLICATE KEY UPDATE) is atomic.
4RETURNING clause (PostgreSQL) fetches modified rows without a second SELECT round-trip.
5TRUNCATE is DDL and resets sequences; DELETE is DML and is row-by-row logged.
6In Spring Data JPA, use @Modifying + @Query for bulk UPDATE/DELETE to avoid loading entities.

Interview Questions

What is the difference between DELETE and TRUNCATE?

EasyAmazon

How does UPSERT work in PostgreSQL and what is its atomicity guarantee?

MediumAtlassian

How would you perform an UPDATE that joins two tables in PostgreSQL vs MySQL?

MediumUber

What is the RETURNING clause and how does it reduce round-trips?

MediumAdobe

How would you safely soft-delete records in a high-traffic system?

HardSwiggy

Ask Aria about INSERT, UPDATE & DELETE

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

Date/Time Functions

INNER JOIN