CRUD Operations

Writing rows#

INSERT INTO customers (name, email, city) VALUES
  ('Asha Rao',   'asha@example.com',  'Pune'),
  ('Ravi Menon', 'ravi@example.com',  'Kochi'),
  ('Meera Iyer', 'meera@example.com', 'Pune'),
  ('Dan Brooks', 'dan@example.com',   NULL);

INSERT INTO products (name, category, price, stock) VALUES
  ('Pen',       'stationery',  10.00, 500),
  ('Notebook',  'stationery',  60.00, 200),
  ('Mouse',     'electronics', 450.00, 40),
  ('Keyboard',  'electronics', 900.00, 25),
  ('Desk Lamp', 'home',        700.00, 15);

Always list the columns. INSERT INTO customers VALUES (...) breaks the day someone adds a column. One multi-row INSERT is also far faster than many single ones, because each statement carries its own parsing and commit overhead.

Seed the order tables too, we'll reuse them all course:

INSERT INTO orders (customer_id, status, ordered_at) VALUES
  (1,'paid','2025-01-05 10:00:00'),
  (1,'shipped','2025-02-10 12:30:00'),
  (2,'new','2025-02-11 09:15:00'),
  (3,'paid','2025-03-01 18:45:00');

INSERT INTO order_items (order_id, product_id, qty, unit_price) VALUES
  (1,1,10,10.00),(1,2,2,60.00),
  (2,3,1,450.00),
  (3,4,1,900.00),(3,1,5,10.00),
  (4,5,1,700.00),(4,3,2,450.00);

Updating#

UPDATE products SET stock = stock - 1 WHERE id = 3;

stock = stock - 1 is evaluated on the row inside the engine. That's safe under concurrency, unlike reading the value in your app, subtracting, and writing it back.

The WHERE you forget#

UPDATE products SET price = 0; changes every row, no warning. Two habits save you:

1. Write the WHERE first, run it as a SELECT, then convert to UPDATE.
2. Run SET sql_safe_updates = 1; in your session. It refuses UPDATE and DELETE without a key in the WHERE.

START TRANSACTION;
UPDATE products SET price = price * 1.10 WHERE category = 'stationery';
SELECT ROW_COUNT();       -- check how many rows you touched
-- looks right? COMMIT; otherwise ROLLBACK;

Deleting#

DELETE FROM order_items WHERE order_id = 3;
DELETE FROM orders WHERE id = 3;

Delete children before parents if your foreign keys are RESTRICT. For huge deletes, do them in batches (DELETE ... LIMIT 5000 in a loop) so you don't hold locks and fill the undo log for one giant transaction.

Upserts#

INSERT INTO products (id, name, category, price, stock)
VALUES (3, 'Mouse', 'electronics', 450.00, 100)
ON DUPLICATE KEY UPDATE stock = VALUES(stock);

If inserting would violate a primary or unique key, MySQL updates the existing row instead. VALUES(col) still works but is deprecated in newer versions in favor of a row alias:

INSERT INTO products (id, name, category, price, stock)
VALUES (3, 'Mouse', 'electronics', 450.00, 100) AS new
ON DUPLICATE KEY UPDATE stock = new.stock;

Note that an upsert on an AUTO_INCREMENT table burns an ID even when it ends up updating. Gaps again.

Common mistakes#

• Forgetting WHERE.
• Reading, computing, and writing a counter in application code instead of in SQL.
• Using REPLACE INTO as an upsert. It deletes and re-inserts, which fires delete cascades and changes the row's id behavior.

Practice#

Insert two new customers in one statement. Raise every electronics price by 5 percent inside a transaction, check ROW_COUNT(), and roll back. Then write an upsert that sets the stock of product 5 to 30, run it twice, and confirm there's still just one row.