INSERT, UPDATE and DELETE

Lesson 18 of 21 · Part 5 · Changing data and tables · about 15 minutes

So far every lesson only read data; the tables never changed. This lesson changes data: adding a row, changing a few cells, removing a few rows.

Every example on this page starts from a fresh copy of the shop's data, so what one example changes is gone in the next. Change things freely. After a change, SELECT again in the same example to see what it did.

INSERT: adding a row

The shop has a new customer. INSERT INTO table (column, column, …) VALUES (value, value, …) puts one row into the table, each value matching a column:

1INSERT INTO customers (id, name, city, joined, email)2VALUES (11, 'Kai Weber', 'Madrid', '2025-10-28', '[email protected]');3 4SELECT * FROM customers WHERE id >= 10;

A statement that changes data has no result table; the page shows Query OK, 1 row affected: it worked, and 1 row was changed. The SELECT after it shows the new customer is in the table.

You can insert several rows at once, each in its own parentheses, separated by commas:

1INSERT INTO products (id, name, category, price, stock)2VALUES (11, 'Sticky notes', 'Stationery', 3.50, 150),3    (12, 'Thermos', 'Kitchen', 29.00, 30);4 5SELECT * FROM products WHERE id > 10;

Columns you leave out

You can list only some of the columns. The ones you leave out get NULL (unless the table gave them a default value; lesson 19 covers that):

1INSERT INTO customers (id, name)2VALUES (11, 'Kai Weber');3 4SELECT * FROM customers WHERE id = 11;

Tip: You can leave out the column names too: INSERT INTO customers VALUES (…) fills the columns in the table's order. But the day the table gets another column, or you remember the order wrong, values land in the wrong place. Naming the columns is safer.

When a row won't go in

Tables have rules. id is the primary key, so no two rows may share one. Product 3 already exists; insert another product 3:

1INSERT INTO products (id, name, category, price, stock)2VALUES (3, 'Umbrella', 'Bags', 35.00, 20);

ERROR 1062 (23000): Duplicate entry '3' for key 'products.PRIMARY'

ERROR 1062 says the value 3 is a duplicate. In the same way, the name column must not be empty (NOT NULL), and putting NULL there gives ERROR 1048. A statement that fails inserts nothing at all. These rules are set when a table is created; lesson 19, where you create your own tables, goes into them.

UPDATE: changing data

UPDATE table SET column = new value WHERE condition changes the rows that match. The notebook's price goes up to 5:

1UPDATE products2SET price = 5.003WHERE id = 1;4 5SELECT id, name, price FROM products WHERE id = 1;

The new value can be worked out from the old one, and you can change several columns at once, separated by commas. New stationery has arrived: 50 more of each, and 5% off:

1UPDATE products2SET stock = stock + 50,3    price = price * 0.954WHERE category = 'Stationery';5 6SELECT name, price, stock FROM products WHERE category = 'Stationery';

price * 0.95 comes out as 4.275, but the price column keeps only two decimal places, so 4.28 is what gets stored.

1UPDATE products2SET stock = stock - 13WHERE category = 'Electronics';

DELETE: removing data

DELETE FROM table WHERE condition removes the rows that match. The desk lamp is out of stock and has never sold, so take it off the shop:

1DELETE FROM products2WHERE stock = 0;3 4SELECT id, name, stock FROM products;

But if a row is still used by another table, the database won't let you remove it. Product 1, the notebook, appears in several orders' items:

1DELETE FROM products2WHERE id = 1;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`town`.`order_items`, CONSTRAINT `order_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`))

ERROR 1451: rows in order_items still point to it, and without the product those order items would no longer say what was bought. This is a foreign key; lesson 19 covers it. To remove the product, you'd first have to remove the rows that point to it.

Forgetting WHERE

WHERE is optional in UPDATE and DELETE, and leaving it out means "every row". You meant to set just one product's price to 0:

1UPDATE products2SET price = 0;3 4SELECT name, price FROM products;

10 rows affected: every price in the table is now 0. DELETE FROM products; is the same: it empties the whole table. In a real database there's no undo for this (unless you use a transaction, lesson 20).

SELECT first, then change

A good habit: before changing anything, write a SELECT with the same WHERE to see which rows it will touch. Once that looks right, swap SELECT … FROM for UPDATE … SET or DELETE FROM:

1SELECT id, status2FROM orders3WHERE customer_id = 1 AND status = 'pending';4 5UPDATE orders6SET status = 'shipped'7WHERE customer_id = 1 AND status = 'pending';8 9SELECT id, status FROM orders WHERE customer_id = 1;

The first query finds only order 9, so that's the only one changed.

Practice

Write the code, then press Check. A hint costs nothing; the answer is there when you need it. The last result table is what's checked, so SELECT again after your change.

1SELECT id, name, city, email2FROM customers3WHERE id >= 94ORDER BY id;

1SELECT name, price2FROM products3WHERE category = 'Kitchen'4ORDER BY id;

1DELETE FROM orders2WHERE id = 4;3 4SELECT id, status5FROM orders6WHERE customer_id = 47ORDER BY id;

What you learned

• INSERT INTO table (column, …) VALUES (…), (…); adds one or more rows; columns you leave out get NULL.
• UPDATE table SET column = value, column = value WHERE …; changes rows; the new value can use the old one.
• DELETE FROM table WHERE …; removes rows; a row another table still points to can't be removed (ERROR 1451).
• Without WHERE, every row is changed (or removed).
• Before a change, run a SELECT with the same WHERE; after it, SELECT to see the result.
• A duplicate primary key is ERROR 1062; NULL in a NOT NULL column is ERROR 1048.