SQL Tutorial #2: INSERT, UPDATE, DELETE — Modifying Data

In the previous article, you learned how to read data with SELECT. But a database is not useful if you cannot add, change, or remove data.

This article covers the three commands that modify data: INSERT, UPDATE, and DELETE. You will also learn about transactions — a way to make sure your changes are safe.

Our Sample Database

We continue with the same online bookstore database. If you need to set it up, copy the CREATE TABLE and INSERT statements from the first article.

INSERT — Adding Rows

INSERT INTO adds new rows to a table.

Insert a single row:

INSERT INTO authors (id, name, country)
VALUES (6, 'Leo Tolstoy', 'Russia');

This adds one author to the authors table. The column names in parentheses tell SQL which columns get which values.

Verify it worked:

SELECT * FROM authors WHERE id = 6;

| id | name        | country |
|----|-------------|---------|
| 6  | Leo Tolstoy | Russia  |

Insert multiple rows at once:

INSERT INTO books (id, title, author_id, price, published_year, in_stock) VALUES
(8, 'War and Peace', 6, 18.99, 1869, 1),
(9, 'Anna Karenina', 6, 16.99, 1877, 1),
(10, 'Brave New World', NULL, 12.99, 1932, 1);

This is faster than three separate INSERT statements. The database processes them in one go.

Insert with default values:

Remember our books table has in_stock INTEGER DEFAULT 1. If you skip this column, it gets the default value:

INSERT INTO books (id, title, author_id, price, published_year)
VALUES (11, 'Fahrenheit 451', NULL, 11.99, 1953);

The in_stock column will automatically be set to 1.

Insert only required columns:

You can skip columns that allow NULL or have default values:

INSERT INTO customers (id, name)
VALUES (5, 'Morgan Lee');

The email and city columns will be NULL.

UPDATE — Changing Existing Data

UPDATE changes data that is already in the table. Always use it with WHERE to target specific rows.

Update one row:

UPDATE books
SET price = 10.99
WHERE id = 1;

This changes the price of “The Great Gatsby” from 12.99 to 10.99.

Verify it worked:

SELECT title, price FROM books WHERE id = 1;

| title            | price |
|------------------|-------|
| The Great Gatsby | 10.99 |

Update multiple columns at once:

UPDATE books
SET price = 7.99, in_stock = 0
WHERE id = 6;

Separate multiple columns with commas.

Update multiple rows:

UPDATE books
SET price = price * 0.9
WHERE published_year < 1950;

This gives a 10% discount to all books published before 1950. You can use the current column value in the calculation.

Update with conditions:

UPDATE books
SET in_stock = 1
WHERE in_stock = 0 AND price < 15;

This restocks all out-of-stock books that cost less than 15 dollars.

The Danger of UPDATE Without WHERE

This is the most important rule in this article:

Always use WHERE with UPDATE.

-- DANGEROUS: This changes EVERY row in the table
UPDATE books SET price = 0;

This sets the price of every book to zero. There is no “undo” button. The data is gone.

If you accidentally run an UPDATE without WHERE, you need to restore from a backup. That is why transactions exist (we will cover them later in this article).

How to Stay Safe: Preview Before You Change

Before running any UPDATE or DELETE, run a SELECT with the same WHERE clause first. This shows you exactly which rows will be affected.

-- Step 1: Preview which rows will be updated
SELECT title, price FROM books WHERE author_id = 3;

| title       | price |
|-------------|-------|
| 1984        | 11.99 |
| Animal Farm | 8.99  |

Two rows. That looks right. Now run the update:

-- Step 2: Update those rows
UPDATE books SET price = price + 1 WHERE author_id = 3;

This habit will save you from many mistakes. It takes five seconds and prevents hours of recovery work.

DELETE — Removing Rows

DELETE FROM removes rows from a table.

Delete one row:

DELETE FROM books WHERE id = 11;

Verify it worked:

SELECT * FROM books WHERE id = 11;

No results. The row is gone.

Delete multiple rows:

DELETE FROM books WHERE in_stock = 0;

This removes all out-of-stock books.

Delete all rows in a table:

DELETE FROM books;

This removes every row but keeps the table structure. The table still exists. It is just empty.

The Danger of DELETE Without WHERE

Just like UPDATE, always use WHERE with DELETE.

-- DANGEROUS: This deletes EVERY row in the table
DELETE FROM customers;

All your customers are gone. No way to get them back without a backup.

DELETE vs TRUNCATE

Both can remove all rows from a table. But they work differently.

-- Delete all rows (slow for large tables, logged, can be rolled back)
DELETE FROM books;

-- Remove all rows (fast, minimal logging, cannot be rolled back in most databases)
TRUNCATE TABLE books;

Use DELETE when you want to remove specific rows or need to undo the operation. Use TRUNCATE when you want to quickly empty a table and start fresh.

Note: SQLite does not support TRUNCATE TABLE. Use DELETE FROM table_name; instead.

Transactions — Keeping Your Data Safe

A transaction is a group of SQL statements that happen together. Either all of them succeed, or none of them happen. There is no in-between.

Why Transactions Matter

Imagine a bank transfer. You need two operations:

1. Take $100 from Alex’s account
2. Add $100 to Sam’s account

What if the database crashes after step 1 but before step 2? Alex lost $100, but Sam never received it. The money disappeared.

Transactions prevent this. You wrap both operations in a transaction. If anything goes wrong, everything is rolled back to the state before the transaction started.

BEGIN, COMMIT, and ROLLBACK

-- Start a transaction
BEGIN;

-- Do your work
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alex';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Sam';

-- If everything looks good, save the changes
COMMIT;

If something goes wrong, you can undo everything:

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE name = 'Alex';

-- Something went wrong! Undo everything
ROLLBACK;

ROLLBACK cancels all changes made since BEGIN. The database goes back to its previous state.

A Practical Example

Let us use transactions with our bookstore:

-- Add a new author and their book in one transaction
BEGIN;

INSERT INTO authors (id, name, country)
VALUES (7, 'Aldous Huxley', 'UK');

INSERT INTO books (id, title, author_id, price, published_year, in_stock)
VALUES (12, 'Brave New World', 7, 12.99, 1932, 1);

-- Both inserts succeeded, save them
COMMIT;

If the second INSERT fails (maybe the book id already exists), you can ROLLBACK and neither the author nor the book will be added.

ACID Properties

Transactions follow four rules known as ACID:

Atomicity — All or nothing. Either all statements in the transaction succeed, or none of them do.

Consistency — The database is always in a valid state. Transactions cannot break the rules you defined (like NOT NULL or UNIQUE constraints).

Isolation — Transactions do not interfere with each other. If two people update the same table at the same time, they do not see each other’s uncommitted changes.

Durability — Once a transaction is committed, the changes are permanent. Even if the server crashes, the data is safe.

You do not need to memorize these. Just remember: transactions make your data safe.

ON CONFLICT / UPSERT

Sometimes you want to insert a row, but if it already exists, update it instead. This is called an upsert (update + insert).

In SQLite and PostgreSQL:

INSERT INTO books (id, title, author_id, price, published_year, in_stock)
VALUES (1, 'The Great Gatsby', 1, 14.99, 1925, 1)
ON CONFLICT(id) DO UPDATE SET
    price = excluded.price,
    in_stock = excluded.in_stock;

This tries to insert a book with id = 1. If a book with that ID already exists, it updates the price and in_stock instead. The excluded keyword refers to the values you tried to insert.

Do nothing on conflict:

INSERT INTO authors (id, name, country)
VALUES (1, 'F. Scott Fitzgerald', 'USA')
ON CONFLICT(id) DO NOTHING;

If the author already exists, nothing happens. No error, no update.

In MySQL, the syntax is different:

INSERT INTO books (id, title, price)
VALUES (1, 'The Great Gatsby', 14.99)
ON DUPLICATE KEY UPDATE price = VALUES(price);

The concept is the same, but the syntax varies between databases.

Putting It All Together

Here is a realistic example. A customer places an order, and you need to:

1. Add the order
2. Update the book’s stock

BEGIN;

-- Create an orders table if it does not exist
CREATE TABLE IF NOT EXISTS orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    book_id INTEGER,
    quantity INTEGER,
    order_date TEXT DEFAULT (date('now'))
);

-- Add the order
INSERT INTO orders (id, customer_id, book_id, quantity)
VALUES (1, 1, 3, 2);

-- Reduce stock
UPDATE books
SET in_stock = in_stock - 1
WHERE id = 3 AND in_stock > 0;

COMMIT;

By wrapping everything in a transaction, you ensure that an order is never created without updating the stock, and stock is never reduced without creating an order.

RETURNING — See What Changed

In PostgreSQL, you can use RETURNING to see the rows that were affected by an INSERT, UPDATE, or DELETE.

-- Insert and see the new row (including auto-generated id)
INSERT INTO authors (id, name, country)
VALUES (8, 'Mark Twain', 'USA')
RETURNING *;

| id | name       | country |
|----|------------|---------|
| 8  | Mark Twain | USA     |

-- Update and see what changed
UPDATE books SET price = price * 0.9
WHERE author_id = 3
RETURNING title, price;

| title       | price |
|-------------|-------|
| 1984        | 10.79 |
| Animal Farm | 8.09  |

-- Delete and see what was removed
DELETE FROM orders WHERE order_date < '2026-02-01'
RETURNING *;

RETURNING is PostgreSQL-specific. SQLite also supports it since version 3.35. MySQL does not support it.

This is very useful in application code. Instead of running an INSERT and then a SELECT to get the new row, you do it in one query.

Common Mistakes

1. UPDATE or DELETE without WHERE

-- You wanted to update one book's price
-- But you forgot the WHERE clause
UPDATE books SET price = 5.99;
-- Now EVERY book costs 5.99

Always double-check that your UPDATE and DELETE statements have a WHERE clause. Run a SELECT with the same WHERE first to see which rows will be affected.

2. Not using transactions for related changes

-- Without a transaction, if the second INSERT fails,
-- you have an author with no book
INSERT INTO authors (id, name, country) VALUES (8, 'New Author', 'USA');
INSERT INTO books (id, title, author_id, price) VALUES (13, 'New Book', 8, 19.99);

Wrap related changes in BEGIN and COMMIT.

3. Inserting the wrong data types

-- price should be a number, not text
INSERT INTO books (id, title, price) VALUES (14, 'Some Book', 'free');

Some databases accept this (SQLite is flexible with types). Others reject it immediately. Always match the column type.

What You Learned

In this article, you learned:

• INSERT INTO adds new rows to a table
• UPDATE changes existing data (always use WHERE)
• DELETE removes rows (always use WHERE)
• TRUNCATE quickly empties a table
• Transactions (BEGIN, COMMIT, ROLLBACK) group changes together
• ACID properties make data safe
• ON CONFLICT / upsert inserts or updates in one statement

Related Articles

• SQL Basics — SELECT, WHERE, and Your First Queries — previous article
• SQL Cheat Sheet — quick reference for all SQL commands

What’s Next?

In the next article, you will learn about JOINs — how to combine data from multiple tables. This is where SQL gets really powerful.