SQL INSERT, UPDATE, DELETE: Data Manipulation Basics

SQL INSERT, UPDATE, and DELETE are the three core statements for writing data to a database. While SELECT lets you read, these Data Manipulation Language (DML) commands let you add, change, and remove rows. In this guide, you'll master all three with clear syntax and real-world examples.

Why Data Manipulation Matters

Every database application eventually needs to add, change, or remove data. While SELECT queries let you read information, the three core Data Manipulation Language (DML) statements — INSERT, UPDATE, and DELETE — let you write to it.

Understanding DML is essential whether you're building applications, managing databases, or preparing for SQL interviews. In this guide, we'll break down each statement with clear syntax, real-world examples using the Chinook digital media database, and tips to avoid common pitfalls.

Note: The SQiLs practice environment runs SELECT queries only. The examples below teach you DML syntax you'll use in production databases, while our practice exercises let you verify data with SELECT queries on the same tables.

Table of Contents

1. INSERT INTO — Adding New Rows
2. UPDATE SET — Modifying Existing Data
3. DELETE FROM — Removing Rows
4. DML Cheat Sheet
5. Common Mistakes to Avoid
6. Practice Exercises
7. Summary
8. FAQs

INSERT INTO — Adding New Rows

The INSERT INTO statement adds one or more new rows to a table.

Basic Syntax

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

Single Row Insert

Imagine adding a new customer to the Chinook Customer table:

INSERT INTO Customer (FirstName, LastName, Email, Country)
VALUES ('Maria', 'Santos', 'maria.santos@example.com', 'Brazil');

Columns you omit will receive their default value (often NULL). The CustomerId primary key auto-increments, so you don't need to specify it.

Multi-Row Insert

You can insert several rows in a single statement:

INSERT INTO Genre (Name)
VALUES
  ('Lo-fi'),
  ('Synthwave'),
  ('Afrobeats');

This is more efficient than running three separate INSERT statements because the database processes it as a single operation.

INSERT INTO ... SELECT

You can also insert rows based on the result of a query:

INSERT INTO PlaylistTrack (PlaylistId, TrackId)
SELECT 18, TrackId
FROM Track
WHERE GenreId = 1;

This copies all Rock tracks into playlist 18 — a powerful pattern for bulk data operations.

UPDATE SET — Modifying Existing Data

The UPDATE statement changes values in existing rows.

Basic Syntax

UPDATE table_name
SET column1 = new_value1,
    column2 = new_value2
WHERE condition;

Single Column Update

Suppose a customer moves to a new city:

UPDATE Customer
SET City = 'Toronto'
WHERE CustomerId = 15;

Multiple Column Update

Updating several columns at once:

UPDATE Customer
SET City = 'Vancouver',
    State = 'BC',
    PostalCode = 'V6B 1A1'
WHERE CustomerId = 15;

Calculated Updates

You can use expressions to compute new values:

UPDATE Track
SET UnitPrice = UnitPrice * 1.10
WHERE GenreId = 1;

This raises the price of all Rock tracks by 10%.

The Critical WHERE Clause

Always include a WHERE clause unless you intentionally want to update every row in the table. Running UPDATE Customer SET Country = 'Canada' without a WHERE clause would change every customer's country — a mistake that's difficult to reverse.

DELETE FROM — Removing Rows

The DELETE statement removes rows from a table.

Basic Syntax

DELETE FROM table_name
WHERE condition;

Delete Specific Rows

Remove a single invoice line item:

DELETE FROM InvoiceLine
WHERE InvoiceLineId = 42;

Delete with a Condition

Remove all invoices from a specific date:

DELETE FROM Invoice
WHERE InvoiceDate < '2009-01-01';

DELETE vs TRUNCATE

Use DELETE when you need to remove specific rows. Use TRUNCATE only when you want to empty an entire table quickly.

The Critical WHERE Clause (Again)

Just like UPDATE, forgetting the WHERE clause in DELETE removes every row in the table. Always double-check your WHERE condition before executing.

DML Cheat Sheet

Transaction Safety Pattern

Wrap DML statements in transactions to make them reversible:

BEGIN TRANSACTION;

UPDATE Customer
SET Email = 'new.email@example.com'
WHERE CustomerId = 1;

-- Verify the change
SELECT CustomerId, Email FROM Customer WHERE CustomerId = 1;

-- If correct:
COMMIT;
-- If wrong:
-- ROLLBACK;

This pattern lets you inspect results before committing, preventing accidental data corruption.

Common Mistakes to Avoid

1. Forgetting the WHERE Clause

The most dangerous DML mistake. Without WHERE, UPDATE changes every row and DELETE removes every row.

-- Dangerous: updates ALL customers
UPDATE Customer SET Country = 'USA';

-- Safe: updates only one customer
UPDATE Customer SET Country = 'USA' WHERE CustomerId = 5;

2. Mismatched Column and Value Count

INSERT requires the number of values to exactly match the number of columns listed.

-- Error: 3 columns but 2 values
INSERT INTO Customer (FirstName, LastName, Email)
VALUES ('John', 'Doe');

-- Correct: 3 columns, 3 values
INSERT INTO Customer (FirstName, LastName, Email)
VALUES ('John', 'Doe', 'john@example.com');

3. Violating Foreign Key Constraints

You can't insert a row that references a non-existent parent record.

-- Fails if ArtistId 9999 doesn't exist
INSERT INTO Album (Title, ArtistId)
VALUES ('New Album', 9999);

-- First verify the parent exists
SELECT ArtistId FROM Artist WHERE ArtistId = 9999;

4. Not Using Transactions for Multi-Step Changes

If one statement in a sequence fails, earlier changes may still persist. Always wrap related DML operations in a transaction.

-- Risky: partial updates if second statement fails
DELETE FROM InvoiceLine WHERE InvoiceId = 10;
DELETE FROM Invoice WHERE InvoiceId = 10;

-- Safe: all-or-nothing
BEGIN TRANSACTION;
DELETE FROM InvoiceLine WHERE InvoiceId = 10;
DELETE FROM Invoice WHERE InvoiceId = 10;
COMMIT;

5. Ignoring NULL Handling in Updates

Setting a column to NULL is different from setting it to an empty string.

-- Sets Company to NULL (unknown)
UPDATE Customer SET Company = NULL WHERE CustomerId = 1;

-- Sets Company to empty string (known but blank)
UPDATE Customer SET Company = '''' WHERE CustomerId = 1;

Choose deliberately based on your data model's conventions.

Practice Exercises

While SQiLs' practice environment focuses on SELECT queries, you can explore the same Chinook tables that these DML examples reference. Use SELECT to examine the data these operations would affect:

These exercises help you understand the data structure so you can confidently write DML statements in production environments.

Summary

The three DML statements form the backbone of database data management:

• INSERT INTO adds new rows — use column lists for clarity and multi-row syntax for efficiency.
• UPDATE SET modifies existing data — always include a WHERE clause and consider transactions.
• DELETE FROM removes rows — double-check your WHERE condition and understand the difference from TRUNCATE.

Master these alongside your SELECT query skills and JOIN knowledge to become a well-rounded SQL developer. Understanding how data gets into a database makes you far more effective at querying it back out.

FAQs

Can I undo an INSERT, UPDATE, or DELETE?

Yes — if you wrap the statement inside a transaction (BEGIN TRANSACTION ... ROLLBACK). Once you issue COMMIT, the changes are permanent. Without an explicit transaction, most databases auto-commit each statement immediately, making it irreversible.

What is the difference between DELETE and TRUNCATE?

DELETE removes specific rows based on a WHERE clause, is fully logged, and fires triggers. TRUNCATE removes all rows from a table with minimal logging and is significantly faster on large tables. Use DELETE for selective removal and TRUNCATE only when you want to empty an entire table.

Can I INSERT into multiple tables at once?

Standard SQL does not support inserting into multiple tables in a single INSERT statement. You need separate INSERT statements for each table. Wrap them in a transaction to ensure they either all succeed or all roll back together.

How do I safely test DML statements before running them?

Use the transaction-and-verify pattern: start a transaction, run your DML statement, then SELECT the affected rows to confirm the result. If everything looks correct, COMMIT. If not, ROLLBACK. Many SQL editors also offer an "explain" or "dry run" mode that shows what would be affected without making changes.