SQL INSERT, UPDATE and DELETE

Reading data is safe; changing it is where mistakes cost money. An UPDATE or DELETE without the right WHERE can wipe out a whole table in a second. This lesson covers the three data-changing statements – and the habits professionals use to run them safely.

These examples change the practice database. Re-run the setup script from chapter 1 afterwards to reset it.

INSERT

-- One row, naming the columns (always name them – it survives table changes)
INSERT INTO customers (customer_id, name, email, city, signup_date)
VALUES (9, 'Isha Menon', 'isha@example.com', 'Chennai', '2025-11-07');

-- Several rows in one statement (much faster than separate INSERTs)
INSERT INTO categories (category_id, name) VALUES
  (5, 'Toys'),
  (6, 'Kitchen');

SELECT customer_id, name, city FROM customers WHERE customer_id = 9;
SELECT * FROM categories WHERE category_id >= 5;

+-------------+------------+---------+
| customer_id | name       | city    |
+-------------+------------+---------+
|           9 | Isha Menon | Chennai |
+-------------+------------+---------+
+-------------+---------+
| category_id | name    |
+-------------+---------+
|           5 | Toys    |
|           6 | Kitchen |
+-------------+---------+

INSERT … SELECT

Copy rows produced by a query – for archives, summary tables or data migrations:

CREATE TABLE delivered_archive (order_id INT PRIMARY KEY, customer_id INT, order_date DATE);

INSERT INTO delivered_archive (order_id, customer_id, order_date)
SELECT order_id, customer_id, order_date
FROM orders
WHERE status = 'delivered' AND order_date < '2025-09-01';

SELECT * FROM delivered_archive;

+----------+-------------+------------+
| order_id | customer_id | order_date |
+----------+-------------+------------+
|      101 |           1 | 2025-08-02 |
|      102 |           2 | 2025-08-05 |
|      103 |           1 | 2025-08-19 |
+----------+-------------+------------+

Upsert: insert or update

When a row might already exist, ON DUPLICATE KEY UPDATE updates it instead of failing with a duplicate-key error – perfect for stock syncs and counters:

INSERT INTO products (product_id, name, category_id, price, stock)
VALUES (1, 'Wireless Mouse', 1, 599.00, 50)
ON DUPLICATE KEY UPDATE stock = stock + 50;          -- product 1 exists: add 50 to its stock

INSERT INTO products (product_id, name, category_id, price, stock)
VALUES (11, 'Yoga Mat', 4, 799.00, 40)
ON DUPLICATE KEY UPDATE stock = stock + 40;          -- product 11 is new: inserted

SELECT product_id, name, stock FROM products WHERE product_id IN (1, 11);

+------------+----------------+-------+
| product_id | name           | stock |
+------------+----------------+-------+
|          1 | Wireless Mouse |   170 |
|         11 | Yoga Mat       |    40 |
+------------+----------------+-------+

(PostgreSQL and SQLite write INSERT … ON CONFLICT (id) DO UPDATE; SQL Server and Oracle use MERGE.)

UPDATE

-- Always: SELECT first with the same WHERE to see which rows you'll change
SELECT product_id, name, price FROM products WHERE category_id = 2;

UPDATE products
SET price = ROUND(price * 1.10, 2)                   -- 10% price rise for Books
WHERE category_id = 2;

SELECT ROW_COUNT() AS rows_changed;
SELECT product_id, name, price FROM products WHERE category_id = 2;

+------------+-------------------+--------+
| product_id | name              | price  |
+------------+-------------------+--------+
|          4 | Clean Code        | 450.00 |
|          5 | SQL in 10 Minutes | 350.00 |
|         10 | JavaScript Guide  | 550.00 |
+------------+-------------------+--------+
+--------------+
| rows_changed |
+--------------+
|            3 |
+--------------+
+------------+-------------------+--------+
| product_id | name              | price  |
+------------+-------------------+--------+
|          4 | Clean Code        | 495.00 |
|          5 | SQL in 10 Minutes | 385.00 |
|         10 | JavaScript Guide  | 605.00 |
+------------+-------------------+--------+

-- Several columns at once, with a CASE
UPDATE orders
SET status = CASE WHEN status = 'shipped' THEN 'delivered' ELSE status END
WHERE order_id IN (106, 109);

SELECT order_id, status FROM orders WHERE order_id IN (106, 109);

+----------+-----------+
| order_id | status    |
+----------+-----------+
|      106 | delivered |
|      109 | delivered |
+----------+-----------+

UPDATE with a join

-- Mark every pending order from Bengaluru customers as 'priority'
UPDATE orders o
JOIN customers c ON c.customer_id = o.customer_id
SET o.status = 'priority'
WHERE o.status = 'pending' AND c.city = 'Bengaluru';

SELECT order_id, customer_id, status FROM orders WHERE status IN ('pending', 'priority');

+----------+-------------+----------+
| order_id | customer_id | status   |
+----------+-------------+----------+
|      108 |           6 | pending  |
|      111 |           4 | priority |
+----------+-------------+----------+

DELETE

DELETE FROM order_items WHERE order_id = 104;        -- children first…
DELETE FROM orders WHERE order_id = 104;             -- …then the parent (the FK requires it)

SELECT COUNT(*) AS orders_left FROM orders;

+-------------+
| orders_left |
+-------------+
|          11 |
+-------------+

Without ON DELETE CASCADE, deleting the order first would fail because order_items still reference it.

Practice

Summary