INSERT, UPDATE & DELETE

Reading data is only half the story. Applications constantly add, change and remove rows. SQL does this with three statements: INSERT, UPDATE and DELETE. They're simple to write and just as easy to get badly wrong, so this lesson also teaches the habits that keep your data safe.

Sample table#

CREATE TABLE products (
    id       INT AUTO_INCREMENT PRIMARY KEY,
    sku      VARCHAR(20)   NOT NULL UNIQUE,
    name     VARCHAR(100)  NOT NULL,
    price    DECIMAL(8,2)  NOT NULL,
    stock    INT           NOT NULL DEFAULT 0,
    category VARCHAR(30)
);

INSERT: adding rows#

Always list the columns. The statement then keeps working if someone later adds a column or reorders them:

INSERT INTO products (sku, name, price, stock, category)
VALUES ('KB-01', 'Mechanical keyboard', 79.00, 12, 'peripherals');

Insert many rows in one statement. This is much faster than one statement per row:

INSERT INTO products (sku, name, price, stock, category) VALUES
    ('MS-01', 'Wireless mouse',   24.50, 40, 'peripherals'),
    ('MN-27', '27" monitor',     229.00,  5, 'displays'),
    ('CB-HD', 'HDMI cable',        7.99, 150, 'cables'),
    ('ST-01', 'Laptop stand',     35.00,  0, NULL);

Columns you leave out get their default: stock becomes 0, and a nullable column with no default becomes NULL. You can also write DEFAULT explicitly as a value.

Getting the generated id. LAST_INSERT_ID() returns the AUTO_INCREMENT value from your connection's latest insert. For a multi-row insert, it returns the id of the first row:

INSERT INTO products (sku, name, price) VALUES ('HB-04', 'USB hub', 19.00);
SELECT LAST_INSERT_ID();

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                6 |
+------------------+

Every driver has an equivalent (cursor.lastrowid in Python, getGeneratedKeys() in Java). Never use SELECT MAX(id) for this: another user might have inserted a row in the meantime.

Copying rows from a query with INSERT ... SELECT:

CREATE TABLE low_stock LIKE products;
INSERT INTO low_stock SELECT * FROM products WHERE stock < 10;
SELECT sku, stock FROM low_stock;

+-------+-------+
| sku   | stock |
+-------+-------+
| MN-27 |     5 |
| ST-01 |     0 |
| HB-04 |     0 |
+-------+-------+

Handling duplicates: IGNORE, upserts and REPLACE#

sku is UNIQUE, so inserting an existing SKU normally fails:

INSERT INTO products (sku, name, price) VALUES ('KB-01', 'Keyboard v2', 85.00);

ERROR 1062 (23000): Duplicate entry 'KB-01' for key 'products.sku'

You have three alternatives:

-- 1. Skip rows that would violate a unique key (turns the error into a warning)
INSERT IGNORE INTO products (sku, name, price) VALUES ('KB-01', 'Keyboard v2', 85.00);

-- 2. Upsert: insert, or update the existing row (MySQL 8.0.19+ row alias syntax)
INSERT INTO products (sku, name, price, stock)
VALUES ('KB-01', 'Mechanical keyboard', 79.00, 3) AS new
ON DUPLICATE KEY UPDATE stock = products.stock + new.stock;

SELECT sku, stock FROM products WHERE sku = 'KB-01';

+-------+-------+
| sku   | stock |
+-------+-------+
| KB-01 |    15 |
+-------+-------+

The upsert found the existing KB-01 and added 3 to its stock (12 → 15). Before 8.0.19 you'd write stock = stock + VALUES(stock). That still works but is deprecated.

The third option, REPLACE INTO, deletes the old row and inserts a new one. That gives it a new AUTO_INCREMENT id and fires delete triggers, so it's rarely what you want. Prefer the upsert.

INSERT IGNORE also silences other errors, such as values that are too long, by converting them to warnings and adjusting the data. Use it only when you really mean "skip duplicates", and check SHOW WARNINGS; afterwards.

UPDATE: changing rows#

UPDATE products
SET price = 69.00,
    stock = stock - 1
WHERE sku = 'KB-01';

• SET can change several columns, and expressions can use the current values (stock - 1).
• WHERE decides which rows change. Leave it out and every row changes.

Update many rows with one condition, such as a 10% price rise on all cables:

UPDATE products SET price = ROUND(price * 1.10, 2) WHERE category = 'cables';
SELECT sku, price FROM products WHERE category = 'cables';

+-------+-------+
| sku   | price |
+-------+-------+
| CB-HD |  8.79 |
+-------+-------+

You can update using another table by joining it in:

CREATE TABLE price_changes (sku VARCHAR(20) PRIMARY KEY, new_price DECIMAL(8,2));
INSERT INTO price_changes VALUES ('MS-01', 22.00), ('MN-27', 199.00);

UPDATE products AS p
JOIN price_changes AS c ON c.sku = p.sku
SET p.price = c.new_price;

SELECT sku, price FROM products WHERE sku IN ('MS-01', 'MN-27');

+-------+--------+
| sku   | price  |
+-------+--------+
| MN-27 | 199.00 |
| MS-01 |  22.00 |
+-------+--------+

DELETE: removing rows#

DELETE FROM products WHERE stock = 0 AND category IS NULL;

That removed the laptop stand. As with UPDATE, a DELETE without WHERE removes every row. To delete rows that match another table, use a join and name the table to delete from:

DELETE p
FROM products AS p
JOIN low_stock AS l ON l.sku = p.sku
WHERE p.stock = 0;

ROW_COUNT() tells you how many rows the previous statement changed, and the mysql client prints it too (Query OK, 1 row affected).

Safe-change habits#

1. SELECT first. Write the WHERE clause as a SELECT, check the rows, then change SELECT * to UPDATE ... SET or DELETE.

2. Use safe-updates mode while learning. It blocks UPDATE/DELETE statements that don't use a key in WHERE (and have no LIMIT):

   SQL

   Copy

   SET SQL_SAFE_UPDATES = 1;
   UPDATE products SET price = 0;

   Output

   ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

   Start the client with mysql --safe-updates to have it on for the whole session.

3. Wrap risky changes in a transaction so you can undo them:

   SQL

   Copy

   SET SQL_SAFE_UPDATES = 0;
   START TRANSACTION;
   DELETE FROM products;          -- oops!
   ROLLBACK;                      -- phew: nothing was deleted
   SELECT COUNT(*) FROM products;

   Output

   +----------+
   | COUNT(*) |
   +----------+
   |        4 |
   +----------+

   You'll learn transactions properly later. For now, remember that ROLLBACK undoes everything since START TRANSACTION.

4. Limit the blast radius. UPDATE ... ORDER BY id LIMIT 1000 changes big tables in small batches, so locks are held briefly.

Common mistakes#

• Quoting numbers and not quoting strings. WHERE sku = KB-01 makes MySQL try to subtract a column called 01 from a column called KB. Strings need quotes: 'KB-01'.
• Column list and value list out of order. Values are matched to columns by position.
• Using = with NULL. WHERE category = NULL never matches anything. Use IS NULL.
• Relying on REPLACE. It deletes and reinserts, so ids change and foreign keys may cascade.

What's next#

Now that you can put data in, let's get good at getting it out. Next: SELECT in depth.