SQL INSERT, UPDATE, DELETE — 14,000 Lost to Missing WHERE

INSERT, UPDATE, and DELETE are the three DML commands that every live application depends on. Every user signup maps to an INSERT. Every profile edit maps to an UPDATE. Every account deletion maps to a DELETE. If you've ever used a web application, these three statements have been running on your behalf dozens of times a day.

The commands themselves are not complicated. A beginner can write a correct INSERT in five minutes. What separates junior from senior engineers is not knowledge of the syntax — it's the operational discipline around execution. A missing WHERE clause on an UPDATE or DELETE is not a recoverable learning exercise in production. It is a P1 incident. Entire salary columns get zeroed. Customer records vanish. Finance teams notice six hours later during batch processing, not immediately, which means the damage window is wide.

I've reviewed post-mortems on incidents exactly like this. The SQL was technically valid. The database executed it correctly. There was no bug. There was just a missing five-character clause, autocommit was on, and there was no pre-execution verification step in the team's process.

This guide covers syntax, execution patterns, and the operational habits that prevent catastrophic data loss — not because the material is exotic, but because the habits have to be built before you're running DML against a production table with ten million rows and no rollback window.

Setting the Scene — The Table We'll Work With Throughout This Guide

Before touching INSERT, UPDATE, or DELETE, we need a stable foundation. Think of a database table like a structured spreadsheet where the column names are fixed by the schema and every row represents one unique real-world entity. The column definitions — their data types, constraints, and nullability — are the contract that every DML statement must honor.

We're going to use an employees table for a fictional company throughout this guide. It's simple enough to understand at a glance but realistic enough to demonstrate the production patterns that actually matter: a primary key that prevents duplicate records, a NOT NULL constraint on the name that enforces data quality, and a DECIMAL type for salary that stores exact financial values without the floating-point rounding errors you'd get from FLOAT.

That last one is worth pausing on. The difference between DECIMAL(10,2) and FLOAT for a salary column is not a pedantic data-type argument. In a payroll system, FLOAT arithmetic introduces rounding errors at the cent level. Multiply a FLOAT salary by a tax rate across ten thousand employees and the accumulated error becomes meaningful. DECIMAL stores exact values. Always use DECIMAL or NUMERIC for financial columns.

Also notice that department is nullable — no NOT NULL constraint. This is intentional. In a real organization, an employee might exist in the system before being assigned to a department (newly onboarded, contractor pending assignment). Forcing NOT NULL on department would cause INSERTs to fail for those legitimate cases. Schema design decisions like this directly determine what DML is possible later.

-- io.thecodeforge: Production employees schema
-- Design decisions documented inline — schema choices constrain DML forever.

CREATE TABLE employees (
    employee_id   INT            PRIMARY KEY,    -- Unique identifier: rejects duplicates at the DB level
    full_name     VARCHAR(100)   NOT NULL,       -- Mandatory: INSERT fails if omitted or NULL
    department    VARCHAR(50),                   -- Nullable: employees can exist before assignment
    salary        DECIMAL(10, 2)                 -- Exact precision: never use FLOAT for financial values
);

-- Verify the schema before writing any DML.
-- In PostgreSQL, \d employees shows columns, types, and constraints.
-- In MySQL, DESCRIBE employees gives the same information.
-- Run this in every target environment before deploying DML scripts.

-- \d employees

-- Confirm the table is empty before the guide begins.
SELECT * FROM employees;

SQL INSERT — Writing New Rows Into Your Table

INSERT is how data enters your system. Every user account, every order, every log event starts life as an INSERT statement somewhere in the call stack. The syntax has one job: map values to columns and write a new row to the table.

There are two patterns you'll use in practice, and they serve different purposes. Single-row INSERT handles real-time events — one user completing a signup form, one sensor publishing a reading, one webhook arriving. Bulk (multi-row) INSERT handles batch operations — seeding a test environment, importing a dataset, processing a queue of queued events in a background job. The performance difference between them is not academic. Single-row INSERTs inside a loop make one network round-trip per row. Bulk INSERT with multi-row VALUES makes one round-trip for the entire batch. For a 10,000-row import, that's the difference between 10,000 individual network calls and one.

The non-negotiable rule: always name your columns explicitly. The position-based form — INSERT INTO employees VALUES (1, 'Sarah', 'Engineering', 72000.00) — works until the moment someone adds a middle_name column between full_name and department. After that schema change, every position-based INSERT either fails with a type mismatch or, worse, silently stores values in the wrong columns. Explicit column naming makes your INSERT statements resilient to schema evolution and self-documenting for anyone reading the code later.

One more pattern worth knowing for production: INSERT ... ON CONFLICT, which handles the case where a row might or might not already exist. Without it, a duplicate primary key causes the entire INSERT to fail. With ON CONFLICT, you can choose to skip the row, update specific columns, or raise a custom error. This is what's commonly called an 'upsert' — a single statement that handles both the insert case and the update case without requiring a SELECT first.

-- io.thecodeforge: Production INSERT patterns

-- PATTERN 1: Single-row INSERT with explicit column names.
-- This is the only safe INSERT form — position-based inserts break on schema changes.
INSERT INTO employees (employee_id, full_name, department, salary)
VALUES (1, 'Sarah Mitchell', 'Engineering', 72000.00);

-- PATTERN 2: Bulk INSERT — one statement, one network round-trip, six rows.
-- Preferred over looping single-row inserts for any batch > 10 rows.
-- Target 500-1000 rows per statement for optimal throughput.
INSERT INTO employees (employee_id, full_name, department, salary)
VALUES
    (2, 'James Okafor',   'Marketing',   55000.00),
    (3, 'Priya Nair',     'Engineering', 68000.00),
    (4, 'Carlos Mendes',  'HR',          48000.00),
    (5, 'Lisa Thornton',  'Engineering', 76000.00),
    (6, 'Derek Huang',    'Marketing',   59000.00);

-- PATTERN 3: Upsert — handles the case where the row may already exist.
-- ON CONFLICT (employee_id): if a row with this primary key already exists...
-- DO UPDATE SET ...: update only the specified columns (not the whole row).
-- EXCLUDED. prefix refers to the VALUES that were attempted in the INSERT.
-- Use this for idempotent operations — safe to run multiple times.
INSERT INTO employees (employee_id, full_name, department, salary)
VALUES (3, 'Priya Nair', 'Engineering', 72000.00)  -- Priya already exists (id=3)
ON CONFLICT (employee_id)
DO UPDATE SET
    salary     = EXCLUDED.salary,
    department = EXCLUDED.department;
-- Result: Priya's salary is updated to 72000.00, no duplicate row created.
-- MySQL equivalent: INSERT ... ON DUPLICATE KEY UPDATE salary = VALUES(salary)

-- Verify all rows after insertion.
SELECT * FROM employees ORDER BY employee_id ASC;

SQL UPDATE — Modifying Existing State Without Breaking It

UPDATE is the command that keeps data current. Password changes, price adjustments, address corrections, status transitions — all of these are UPDATEs under the hood. The mechanism is straightforward: identify which rows to change (WHERE), specify what to change them to (SET), and the database engine applies the modification atomically to every matched row.

The WHERE clause is where the danger lives. Without it, 'every matched row' means 'every row in the table.' The database engine doesn't distinguish between 'I want to update one row' and 'I want to update all rows' — it executes whatever you tell it to. A missing WHERE clause on a 10,000-row employees table is not caught by the SQL parser, not caught by the query planner, and not caught by the storage engine. It executes correctly and commits instantly if autocommit is on. The only thing that catches it is you, before you run it.

The SELECT-first workflow is the habit that prevents incidents. Before writing an UPDATE, write the SELECT equivalent first: SELECT FROM employees WHERE employee_id = 1. Verify that the result set contains exactly the rows you intend to modify. Count them. Then, and only then, convert the statement to an UPDATE by replacing SELECT with UPDATE employees SET salary = ... . This takes an extra 10 seconds. Over a career, it will save you hours.

For batch UPDATEs affecting large numbers of rows, the performance concern shifts from correctness to concurrency. An UPDATE that modifies 5 million rows in a single statement holds row-level locks on all affected rows for the duration of the operation — potentially minutes. Other transactions trying to write to any of those rows queue behind it. On a busy system, this causes cascading timeout failures across your API layer. The mitigation is batching: update 10,000 rows at a time with a COMMIT between batches, keeping the lock window short enough that concurrent writes are not blocked for an unacceptable duration.

-- io.thecodeforge: Production UPDATE patterns

-- PATTERN 1: Single-row targeted update via Primary Key.
-- Primary Key in WHERE ensures exactly one row is matched — the safest UPDATE pattern.
UPDATE employees
SET salary = 80000.00
WHERE employee_id = 1;  -- WHERE clause written first — non-negotiable habit
-- Verify: SELECT full_name, salary FROM employees WHERE employee_id = 1;

-- PATTERN 2: Multi-column update in one statement.
-- Multiple SET assignments separated by commas — one atomic operation.
-- Carlos is moving from HR to Engineering with a corresponding salary adjustment.
UPDATE employees
SET
    department = 'Engineering',
    salary     = 62000.00
WHERE employee_id = 4;

-- PATTERN 3: Conditional batch update — applying logic to a segment.
-- Increase Marketing salaries by 10%. salary * 1.10 uses the current value.
-- SELECT-first verification step (run this, confirm row count, then UPDATE):
-- SELECT full_name, salary, salary * 1.10 AS new_salary
-- FROM employees WHERE department = 'Marketing';
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Marketing';

-- PATTERN 4: Safe batch UPDATE for large tables — update in chunks.
-- Never run a multi-million-row UPDATE in a single transaction.
-- This example updates 1000 rows per batch, releasing locks between commits.
-- In a real script this would be a loop with a LIMIT and OFFSET or cursor.
UPDATE employees
SET salary = salary * 1.05
WHERE department = 'Engineering'
  AND employee_id BETWEEN 1 AND 1000;  -- batch boundaries
-- COMMIT; -- release locks, then run next batch

-- Audit the changes after all patterns.
SELECT
    employee_id,
    full_name,
    department,
    salary
FROM employees
ORDER BY employee_id;

SQL DELETE — Removing Records Safely and Responsibly

DELETE removes entire rows from a table. Not a column. Not a value. The entire row, with all its data, removed from the storage engine. This distinction matters: if you want to clear a value in a specific column while keeping the row, you use UPDATE SET column = NULL. DELETE is for when the record itself should no longer exist.

DELETE is irreversible without a transaction or a backup. Unlike UPDATE, where the old values might be recoverable from audit logs or CDC streams, a committed DELETE removes the row from the table immediately. Vacuum processes in PostgreSQL and InnoDB compaction in MySQL will eventually reclaim the physical disk space, but from the application's perspective, the data is gone the moment the DELETE commits.

This permanence is why most production systems for user-facing data default to Soft Delete rather than Hard Delete. A Soft Delete doesn't use the DELETE command at all — it uses UPDATE to set an is_deleted flag to true or a deleted_at timestamp to the current time. The row remains in the table, queries that should exclude deleted records add WHERE is_deleted = false or WHERE deleted_at IS NULL, and the data can be restored by flipping the flag back. The trade-off is table bloat over time, which requires periodic archival jobs to move soft-deleted rows to cold storage.

Hard Delete has legitimate use cases: GDPR right-to-erasure requests (where soft delete doesn't satisfy the legal requirement to actually remove data), clearing temporary staging tables that are re-populated on each run, and removing log or event data past its retention window. In all three cases, the DELETE should be inside a transaction, preceded by a SELECT to verify the target rows, and — for large tables — executed in batches to avoid holding locks for an unacceptable duration.

The batching requirement for large DELETEs is more critical than for large UPDATEs. Every deleted row generates a transaction log entry proportional to the row size. A single DELETE of 50 million rows generates enough WAL or redo log volume to fill disk on many systems. Batching with COMMIT between chunks keeps the per-transaction log volume manageable and keeps the lock window short enough for other writes to proceed.

-- io.thecodeforge: Production DELETE patterns

-- PATTERN 1: Targeted Hard Delete via Primary Key
-- Primary Key in WHERE guarantees exactly one row is affected — the safest DELETE pattern.

-- Step 1: Always verify the target row before deleting.
SELECT * FROM employees WHERE employee_id = 2;
-- Verify this is James Okafor. Confirm there is exactly one row.

-- Step 2: Delete only after verifying the target.
DELETE FROM employees
WHERE employee_id = 2;  -- Primary Key: guaranteed one row, no ambiguity

-- PATTERN 2: Soft Delete — the default for user-facing data
-- Requires an is_deleted column (ALTER TABLE employees ADD COLUMN is_deleted BOOLEAN DEFAULT false)
-- Never physically removes the row — just hides it from application queries.
-- This example is illustrative; it works once the column exists.
-- UPDATE employees
-- SET is_deleted = true, deleted_at = NOW()
-- WHERE employee_id = 2;
-- Restore: UPDATE employees SET is_deleted = false, deleted_at = NULL WHERE employee_id = 2;

-- PATTERN 3: Batch delete by category
-- Add temporary staging rows to demonstrate scoped batch delete.
INSERT INTO employees (employee_id, full_name, department, salary)
VALUES
    (7, 'Angela Foster', 'HR', 47000.00),
    (8, 'Ben Stafford',  'HR', 44000.00);

-- Step 1: Count affected rows — verify before deleting.
SELECT COUNT(*) FROM employees WHERE department = 'HR';
-- Expected: 2 rows (Angela and Ben — Carlos moved to Engineering in the UPDATE section)

-- Step 2: Wrap in a transaction for safety. Verify row count inside BEGIN.
BEGIN;
    DELETE FROM employees
    WHERE department = 'HR';
    -- The database engine returns the rows-affected count here.
    -- If it matches your expectation from the COUNT above, proceed.
COMMIT;
-- If the count is wrong: ROLLBACK; instead of COMMIT;

-- PATTERN 4: Batched DELETE for large tables (pseudocode structure)
-- Never delete millions of rows in one transaction.
-- Loop until no rows remain in the target set:
-- DELETE FROM employees
-- WHERE department = 'archived'
--   AND ctid IN (
--       SELECT ctid FROM employees
--       WHERE department = 'archived'
--       LIMIT 10000
--   );
-- COMMIT; -- release locks between batches

-- Final state after all deletes.
SELECT * FROM employees ORDER BY employee_id;