SQL INSERT, UPDATE, DELETE: Complete DML Guide

1. Understanding Data Manipulation Language (DML)

Data Manipulation Language (DML) consists of SQL commands used to modify and manage data stored within existing relational tables. While Data Definition Language (DDL) creates and alters the table structures, DML populates, edits, and removes the actual row records.

The core DML commands are INSERT, UPDATE, and DELETE. Unlike DDL, DML statements operate inside transaction blocks, meaning their modifications can be committed permanently or rolled back completely if errors occur.

Command	Operation Type	Target Scope	Transactional Rollback
INSERT	Creates new records	Appends one or more rows to a table	Fully rollbackable within transactions
UPDATE	Modifies existing records	Changes specified column values in matching rows	Fully rollbackable within transactions
DELETE	Removes existing records	Deletes specified rows matching filter criteria	Fully rollbackable within transactions

`The ACID Guarantee in DML`

Every DML modification is governed by database transaction rules:

Atomicity:A multi-row insert or batch update succeeds entirely or fails completely. No partial state is saved if a constraint violation triggers midway.
Consistency:DML statements cannot violate table constraints (NOT NULL, UNIQUE, FOREIGN KEY, CHECK). Any violation causes the entire statement to abort.
Isolation:Concurrent transactions cannot see uncommitted DML modifications made by other sessions until a COMMIT executes.

2. INSERT INTO Statement

The INSERT INTO statement adds new rows of data into an existing database table. You can specify columns explicitly or rely on the table ordinal column order.

Single-Row INSERT INTO Syntax

SQL

Insert records using explicit column lists and observe automatic default assignments

`Explicit Columns vs Positional Insert`

Why you should always write out the explicit column list:

Positional Risk:Writing INSERT INTO books VALUES (1, 'Title', 'Author', 29.99) breaks immediately if a later migration adds a new column or reorders fields.
Explicit Safety:Listing column names ensures that future schema modifications (such as adding nullable or default columns) will not break existing insert scripts.
Self-Documenting Code:Explicit column definitions make backend database queries easier to debug and audit during peer code reviews.

3. Insert Multiple Rows (Batch Insertion)

Instead of executing multiple separate INSERT statements, SQL allows batch insertion by separating row tuples with commas within a single VALUES clause.

Batch Inserting Multiple Rows

SQL

Populate multiple records in a single statement to minimize network and logging overhead

`Performance Advantage of Batch Inserts`

Batch insertion yields dramatic throughput gains in production environments:

Round-Trip Reduction:Inserting 1,000 rows individually requires 1,000 network round trips between application and database. A batch insert requires exactly one.
Single Write-Ahead Log (WAL) Flush:Batching writes everything into a single transaction commit, reducing disk I/O flushes from 1,000 writes to 1.
Parameter Limits:Most engines enforce limits on query parameters (SQLite permits up to 32,766 parameters; SQL Server permits 2,100). Split multi-thousand row inserts into chunks of 500 to 1,000 rows.

4. INSERT INTO SELECT (Copying Data from Another Table)

The INSERT INTO ... SELECT statement populates an existing table directly from the result set of a query. This is a primary tool for ETL pipelines, data archiving, and staging tables.

Populating Tables with Query Results

SQL

Extract, transform, and insert data from an existing table into an archive relation

`INSERT INTO SELECT vs CREATE TABLE AS SELECT (CTAS)`

Understanding when to use INSERT INTO SELECT over CTAS:

Target Table Exists:INSERT INTO SELECT requires the target table to exist beforehand, allowing you to preserve custom indexes, foreign keys, and default values.
Incremental Population:CTAS creates a brand new table from scratch, whereas INSERT INTO SELECT can run repeatedly on schedules to append fresh data.
Column Mapping:Data types in the SELECT column list must align with the target table schema positions, or explicit column lists must be supplied.

5. UPDATE Statement

The UPDATE statement modifies existing column values for rows that satisfy a specified condition. If the WHERE clause is omitted, every row in the table is updated.

Single-Row UPDATE Operation

SQL

Target a specific record by primary key and modify stored column values

`The Dangerous Missing WHERE Clause`

Executing UPDATE without a WHERE filter applies the change to all records:

Accidental Table Wipe:Executing UPDATE users SET role = 'admin'; without WHERE promotes every user in your database to administrator.
Pre-Flight Verification Pattern:Always write your filter as a SELECT first (e.g. SELECT * FROM users WHERE id = 42). Once you verify the matched row count, change SELECT * to UPDATE users SET.
Safe Update Flags:In MySQL, setting SQL_SAFE_UPDATES = 1 rejects any UPDATE statement that does not specify a key in the WHERE clause.

6. UPDATE Multiple Columns

You can modify multiple columns in a single statement by separating column assignments with commas inside the SET clause.

Updating Multiple Columns Concurrently

SQL

Assign multiple values and relative increments within a single atomic statement

`Relative Increments and Self-Referencing SET`

SQL evaluates all assignments in the SET clause using the original row state:

Atomic Expressions:Expressions like SET stock = stock + 10 calculate relative adjustments safely against the current database value.
Concurrency Safety:Relative calculations inside SQL transactions prevent lost updates that happen when applications read, calculate in memory, and overwrite.
Cross-Field Assignments:In standard SQL, if you write SET colA = colB, colB = colA, both columns read the pre-update values of the row.

7. UPDATE with JOIN

Real-world business logic frequently requires updating one table based on data values or relationships residing in another table. Different SQL database engines implement cross-table updates through distinct syntactic patterns:

Engine	Syntax Pattern	Execution Mechanism
PostgreSQL / SQLite	UPDATE t1 SET col = t2.val FROM t2 WHERE t1.id = t2.id;	Utilizes FROM clause to join secondary source table
MySQL / MariaDB	UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.col = t2.val;	Places explicit JOIN directly between UPDATE and SET clauses
SQL Server	UPDATE t1 SET t1.col = t2.val FROM t1 JOIN t2 ON t1.id = t2.id;	References target alias followed by FROM JOIN clause

Cross-Table UPDATE Using FROM Clause

SQL

Synchronize column values across tables using relational joins

`Multiple Matches in Target Joins`

Beware of one-to-many joins in update statements:

Non-Deterministic Updates:If table2 contains multiple rows matching a single row in table1, the database updates table1 with one arbitrary matching value. The exact winning value is undefined.
Pre-Aggregation Rule:When joining against a child table with multiple rows, aggregate the child records first (e.g. using MAX, SUM, or subqueries) before joining in the UPDATE.

8. DELETE Statement

The DELETE statement removes existing records from a table. Unlike DROP TABLE, which destroys the schema definition, DELETE removes rows while keeping the table definition, columns, indexes, and constraints intact.

Removing Records with DELETE

SQL

Delete rows matching specific conditions while preserving the table structure

`DELETE vs TRUNCATE Mechanics`

A quick refresher on why DELETE behaves differently than TRUNCATE:

Row-Level Logging:DELETE records an individual entry for every deleted row in the database transaction log, allowing point-in-time recovery and triggers.
Triggers Fire:DELETE activates all defined ON DELETE triggers for every affected record.
Identity Counter:DELETE does not reset AUTO_INCREMENT counters; the sequence continues counting upward.

9. DELETE with WHERE (Safe Row Removal)

The WHERE clause narrows the scope of the deletion. Without a WHERE condition, every row in the target table is permanently deleted.

Targeted Data Deletion with Filters

SQL

Filter records by date comparison and purge only matching historical rows

`Defensive Deletion Workflow`

Follow these four defensive steps before executing DELETE in production:

Step 1: Write as SELECT:Run SELECT COUNT(*), * FROM table WHERE condition to verify the exact rows about to be removed.
Step 2: Wrap in a Transaction:Run BEGIN TRANSACTION; DELETE FROM table WHERE condition; SELECT COUNT(*) FROM table; before committing.
Step 3: Check Row Count:Verify the database 'rows affected' response matches your expectation. If 500 rows were deleted instead of 5, execute ROLLBACK immediately.
Step 4: Commit:Execute COMMIT only after verifying data integrity.

10. DELETE with JOIN

Deleting records based on conditions in a related table is common in data cleanup tasks (for example, removing orders associated with banned customers). Syntax varies across database engines:

Engine	Syntax Pattern	Notes
PostgreSQL	DELETE FROM orders USING customers WHERE orders.cust_id = customers.id AND customers.is_banned = 1;	Uses USING clause to join secondary table
MySQL	DELETE o FROM orders o JOIN customers c ON o.cust_id = c.id WHERE c.is_banned = 1;	Specifies target alias before the FROM clause
Standard / SQLite	DELETE FROM orders WHERE cust_id IN (SELECT id FROM customers WHERE is_banned = 1);	Subquery filter supported uniformly across all engines

Deleting Rows Using Cross-Table Filters

SQL

Purge records from a child table based on status values in a parent table

`Subquery vs Native Multi-Table DELETE`

Choosing the right pattern for multi-table deletions:

Universal Portability:The WHERE column IN (SELECT ...) pattern works identically across PostgreSQL, MySQL, SQL Server, Oracle, and SQLite.
EXISTS Alternative:For large tables, WHERE EXISTS (SELECT 1 FROM ...) often performs faster than IN by allowing the query optimizer to use indexed semi-joins.

11. UPSERT (INSERT ... ON CONFLICT / ON DUPLICATE KEY UPDATE)

An UPSERT (a portmanteau of UPDATE and INSERT) attempts to insert a record into a table, but if a primary key or unique constraint conflict occurs, it automatically updates the existing record instead of throwing a constraint error.

Engine	Syntax Clause	Conflict Target
PostgreSQL / SQLite	ON CONFLICT (key) DO UPDATE SET col = EXCLUDED.col	Explicit unique column or constraint name
MySQL / MariaDB	ON DUPLICATE KEY UPDATE col = VALUES(col)	Implicitly catches any primary or unique key collision

UPSERT via ON CONFLICT DO UPDATE

SQL

Gracefully handle duplicate key collisions by incrementing existing counters

`The EXCLUDED Virtual Table`

In PostgreSQL and SQLite UPSERT statements, the EXCLUDED keyword represents proposed values:

What EXCLUDED Represents:EXCLUDED.column contains the value that would have been inserted if no conflict occurred.
DO NOTHING Option:You can write ON CONFLICT (key) DO NOTHING to silently ignore duplicates without modifying existing records.
Eliminating Race Conditions:UPSERT is atomic at the database storage engine layer, eliminating race conditions that occur in application code checking SELECT then INSERT.

12. MERGE Statement (ANSI SQL Standard)

The MERGE statement is the ANSI SQL standard method for synchronizing two tables. It joins a target table with a source table and conditionally performs INSERT, UPDATE, or DELETE actions based on whether matching rows exist.

Table Synchronization and MERGE Semantics

SQL

Observe how target tables synchronize with batch inputs based on match conditions

`MERGE Statement Capabilities`

Why enterprise data warehouses rely heavily on MERGE statements:

Single Statement Synchronization:Replaces separate INSERT, UPDATE, and DELETE scripts with a single unified data pipeline statement.
WHEN MATCHED AND ... THEN DELETE:MERGE supports deleting target rows if source records indicate an account cancellation or status flag.
Engine Adoption:Supported natively in Oracle, Microsoft SQL Server, Snowflake, BigQuery, and added to PostgreSQL in version 15.

13. REPLACE INTO Statement

The REPLACE INTO statement is a specialized SQL extension supported by MySQL and SQLite. It operates as a shortcut: if a row with the same primary key or unique index already exists, the database deletes the existing row and inserts the new one.

REPLACE INTO Shortcut in Action

SQL

Overwrite conflicting records completely without writing separate delete statements

`The Hidden Cost of REPLACE INTO`

Be aware of the internal side effects of REPLACE INTO compared to UPSERT:

Internal DELETE + INSERT:Because the old row is physically deleted, any columns omitted from the REPLACE statement revert to their DEFAULT values or NULL rather than keeping previous values.
Foreign Key Deletion Triggers:If child tables have ON DELETE CASCADE foreign keys referencing this table, REPLACE INTO will delete all child rows before inserting the new parent!
Auto-Increment Counter Jump:If the replaced table uses an auto-increment primary key, the row receives a brand new, higher ID number rather than preserving its original identity.
Best Practice:Prefer standard UPSERT (ON CONFLICT DO UPDATE) over REPLACE INTO in production schemas.

Knowledge Check: SQL INSERT, UPDATE, DELETE

1. What happens if you execute an UPDATE statement without a WHERE clause?

2. Why is batch inserting multiple rows in one statement faster than separate statements?

3. What does the statement INSERT INTO ... SELECT accomplish?

4. How does UPSERT handle a primary key duplicate collision?

5. What is the underlying operation of the REPLACE INTO statement?

6. In PostgreSQL, which clause enables cross-table updates?