SQL INSERT, UPDATE and DELETE

Add new rows, change existing data and remove rows safely, without losing anything by accident.

Lesson 8 of 102Beginner19 min read

So far you've only read data. Real applications also need to write it: a new customer signs up, a price changes, a cancelled order is removed. SQL has three statements for this: INSERT adds rows, UPDATE changes them and DELETE removes them.

Together with SELECT, these make up the four basic operations every app performs, often called CRUD: Create, Read, Update and Delete.

Changing data is powerful, and mistakes are easy to make: one missing WHERE can change every row in a table. In this lesson you'll learn the three statements and a simple habit that keeps your data safe.

Adding rows with INSERT

SQL
INSERT INTO customers (name, city, joined)
VALUES ('Finn', 'Dublin', '2024-08-12');
  • After INSERT INTO, name the table and, in brackets, the columns you're filling.
  • VALUES lists the values in the same order as the columns.
  • You usually leave out id. When it's an auto-numbered primary key, the database chooses the next number for you.
  • Columns you don't mention get their default value, or NULL.

You can add several rows in one statement by separating their value lists with commas: VALUES ('Gia', 'Rome', '2024-08-20'), ('Hal', 'Oslo', '2024-08-21');

Changing rows with UPDATE

SQL
UPDATE products
SET stock = 50
WHERE id = 5;
  • UPDATE names the table.
  • SET lists the new values. Change several columns at once with commas: SET price = 9.50, stock = 60.
  • WHERE chooses which rows to change. Only rows that match are updated.

The new value can be calculated from the old one: SET price = price * 1.1 raises a price by 10 percent.

Removing rows with DELETE

SQL
DELETE FROM orders
WHERE id = 6;

DELETE FROM removes whole rows that match the WHERE condition. There's no "undo" button in most database tools, so deleted rows are gone unless you have a backup.

To remove a single value rather than a row, use UPDATE to set it to NULL instead.

The safety habit: SELECT first

Always write the WHERE clause first, and test it with a SELECT.

SQL
-- 1. Check which rows will be affected
SELECT * FROM products WHERE category = 'Stationery';

-- 2. Happy with the result? Reuse the same WHERE
UPDATE products SET price = price * 1.1 WHERE category = 'Stationery';

UPDATE and DELETE without a WHERE clause affect every row in the table. Many professionals also wrap changes in a transaction with BEGIN; and COMMIT;, so they can run ROLLBACK; to cancel if the result looks wrong.

Inserting rows from a query

INSERT can also take its rows from a SELECT, which copies or archives data in one statement:

SQL
INSERT INTO archived_orders (id, customer_id, product_id, quantity, order_date)
SELECT id, customer_id, product_id, quantity, order_date
FROM orders
WHERE order_date < '2024-01-01';

The columns in the SELECT must match the listed columns in number and order. Run the SELECT on its own first to check exactly which rows will be copied.

Seeing what changed with RETURNING

PostgreSQL, and SQLite from version 3.35, can return the affected rows straight from a write statement:

SQL
INSERT INTO customers (name, city, joined)
VALUES ('Finn', 'Dublin', '2024-08-12')
RETURNING id, name;

This is handy for getting the new automatic id without a second query, and UPDATE ... RETURNING and DELETE ... RETURNING show exactly which rows were changed or removed. MySQL doesn't support RETURNING; programs read the new id through their database driver instead.

SQL INSERT, UPDATE and DELETE Syntax

INSERT, UPDATE, DELETE

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

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

DELETE FROM table_name
WHERE condition;

SQL INSERT, UPDATE and DELETE Summary

  • INSERT INTO table (cols) VALUES (...) adds new rows.
  • UPDATE table SET col = value WHERE ... changes existing rows.
  • DELETE FROM table WHERE ... removes rows permanently.
  • Without WHERE, UPDATE and DELETE affect every row.
  • Test your WHERE with SELECT first, and target single rows by their id.

SQL INSERT, UPDATE and DELETE Examples

Each example shows the code, the output it produces and what each part does.

Example 1

Add a new customer

SQL
INSERT INTO customers (name, city, joined)
VALUES ('Finn', 'Dublin', '2024-08-12');

SELECT * FROM customers WHERE name = 'Finn';
Output
1 row inserted.

id | name | city   | joined
---+------+--------+-----------
6  | Finn | Dublin | 2024-08-12
Line-by-line explanation
  1. Lines 1 to 2The id column is left out, so the database gives Finn the next number, 6.
  2. Line 4A quick SELECT confirms the new row is there.
Example 2

Restock one product

SQL
UPDATE products
SET stock = 50
WHERE id = 5;

SELECT name, stock FROM products WHERE id = 5;
Output
1 row updated.

name      | stock
----------+------
Lunch box | 50
Line-by-line explanation
  1. Line 3Targeting the primary key changes exactly one row: the lunch box.
Example 3

Raise prices in one category

SQL
UPDATE products
SET price = ROUND(price * 1.1, 2)
WHERE category = 'Stationery';

SELECT name, price FROM products WHERE category = 'Stationery';
Output
2 rows updated.

name     | price
---------+------
Notebook | 3.85
Pen      | 1.32
Line-by-line explanation
  1. Line 2The new price is calculated from the old one: 10 percent more, rounded to 2 decimals.
  2. Line 3Only the two stationery products change.
Example 4

Remove old orders

SQL
DELETE FROM orders
WHERE order_date < '2024-07-02';
Output
1 row deleted.
Line-by-line explanation
  1. Line 2Only order 1, placed on 2024-07-01, is earlier than the date given.

Common SQL INSERT, UPDATE and DELETE Mistakes

UPDATE without WHERE

Don't
UPDATE products SET price = 0;
Do
UPDATE products SET price = 0 WHERE id = 2;

The first statement sets the price of every product to 0.

Values in the wrong order

Don't
INSERT INTO customers (name, city, joined)
VALUES ('Leeds', 'Gia', '2024-09-01');
Do
INSERT INTO customers (name, city, joined)
VALUES ('Gia', 'Leeds', '2024-09-01');

Values are matched to columns by position, so the first version saves a customer called "Leeds" who lives in "Gia".

DELETE when you meant to clear one value

Don't
DELETE FROM customers WHERE id = 3;
Do
UPDATE customers SET city = NULL WHERE id = 3;

If you only want to forget Chloe's city, update that column. DELETE removes the whole customer.

SQL INSERT, UPDATE and DELETE in Real Projects

Writing data in apps

When you create an account on a website, the server runs an INSERT. When you change your email address, it runs an UPDATE ... WHERE id = your_id. When you delete a post, it runs a DELETE. Apps build these statements with parameters rather than pasting user input into the SQL text, which protects the database from an attack called SQL injection.

SQL INSERT, UPDATE and DELETE FAQ

What is the difference between DELETE, TRUNCATE and DROP?

DELETE removes the rows that match a WHERE condition, or all rows without one, and the table stays. TRUNCATE, available in most server databases, quickly empties the whole table. DROP TABLE removes the table itself, structure and all. Only DELETE lets you choose which rows go.

How do I undo an UPDATE or DELETE?

Inside a transaction, run ROLLBACK before COMMIT and the change disappears, as the transactions lesson shows. Once a change is committed, most databases have no undo, so the only way back is a backup. Test the WHERE clause with a SELECT before every risky change.

How do I insert a row or update it if it already exists?

That's called an upsert. PostgreSQL and SQLite use INSERT ... ON CONFLICT (sku) DO UPDATE SET stock = stock + excluded.stock, and MySQL uses INSERT ... ON DUPLICATE KEY UPDATE. Both need a unique constraint on the column that identifies a duplicate.

How do I update rows using values from another table?

Use a subquery that works everywhere, such as UPDATE products SET stock = (SELECT ...) WHERE ..., or your database's join syntax: UPDATE ... FROM in PostgreSQL and SQLite, and UPDATE products JOIN deliveries ON ... SET ... in MySQL. Check the matching rows with a SELECT first.

Why does my INSERT fail?

Common reasons are a missing value for a NOT NULL column, a duplicate value in a primary key or unique column, a foreign key pointing to a row that doesn't exist, or values listed in a different order from the columns. The error message names the constraint, which tells you which rule was broken.

SQL INSERT, UPDATE and DELETE Exercises

Answer each one and check it instantly. If you get stuck, open the hint before the answer.

Fill in the blankseasy

Complete the statement to add a product called Ruler costing 2.10.

 products (name, category, price, stock)
 ('Ruler', 'Stationery', 2.10, 80);
Fill in the blanksmedium

Complete the statement so only Ben's city changes to York.

UPDATE customers
 city = 'York'
 id = 2;
Predict the outputmedium

How many rows does this change in the example products table?

UPDATE products SET stock = stock + 10 WHERE stock < 30;
Multiple choicemedium

What's the safest first step before running a DELETE?

Multiple choicehard

What happens if you run DELETE FROM orders; with no WHERE clause?

SQL INSERT, UPDATE and DELETE Quiz

5 questions to check what you learned.

  1. Which statement adds a new row?

    • ADD
    • UPDATE
    • INSERT
    • CREATE ROW

  2. What happens if you run DELETE FROM orders; with no WHERE?

    • Nothing, WHERE is required
    • Only the last row is deleted
    • The orders table itself is removed
    • Every row in orders is deleted

  3. What does CRUD stand for?

    • Copy, Run, Undo, Drop
    • Create, Run, Use, Delete
    • Create, Read, Update, Delete
    • Count, Read, Update, Debug

  4. Which statement increases every Kitchen price by 2?

    • UPDATE products price + 2 WHERE category = 'Kitchen';
    • UPDATE products SET price = price + 2 WHERE category = 'Kitchen';
    • INSERT INTO products SET price = price + 2;
    • SET products.price = price + 2 WHERE category = 'Kitchen';

  5. Which statement changes existing rows?

    • UPDATE
    • INSERT
    • SELECT
    • ALTER

Related SQL topics

03WHERE (Filtering)Return only the rows you need with comparisons, AND, OR, NOT, IN, BETWEEN, LIKE and IS NULL.12JOINCombine rows from related tables with INNER JOIN and LEFT JOIN, using keys and table aliases.
Esc
↑↓ to navigateEnter to open