Chapter 3 · Creating and Changing Data
INSERT, UPDATE, DELETE and Upserts, Safely
- Page 12 of 22
- 15 min read
Reading data is safe: a bad SELECT just shows the wrong rows. Changing data is not. INSERT, UPDATE and DELETE are the DML (data manipulation language) statements, and they are how every application writes to its database: a new sign-up, a price change, a cancelled order. In AI work they are how you log every prediction a model makes, store the labels a human annotator gives, and correct the bad records you find while cleaning data.
This page teaches the statements and, just as important, a routine for running them without fear: check first, change inside a transaction, verify, then commit.
What you will learn
INSERTwith a column list, several rows at once, andINSERT … SELECT.UPDATE … SET … WHEREandDELETE … WHERE, and how constraints guard them.- Upserts:
ON CONFLICT(SQLite, PostgreSQL) andON DUPLICATE KEY UPDATE(MySQL), plusRETURNING. - A safe routine for changing data:
SELECTfirst,BEGIN/ROLLBACK, row counts, backups. - Writing data from Python with parameters,
executemanyandcommit.
CRUD: the four things you do to data
| Operation | SQL | In a web API | Shop example |
|---|---|---|---|
| Create | INSERT | POST | A new customer signs up |
| Read | SELECT | GET | Show a customer's orders |
| Update | UPDATE | PUT / PATCH | Restock a product |
| Delete | DELETE | DELETE | Remove an account |
INSERT: adding rows
Always list the columns. Then the statement keeps working if someone adds a column or changes the column order, and anyone reading it knows which value goes where. Several rows go in one statement, separated by commas:
INSERT INTO customers (customer_id, name, email, city, joined_on)
VALUES (9, 'Rina Begum', 'rina@example.com', 'Dhaka', '2026-06-20'),
(10, 'Arif Mollah', 'arif@example.com', NULL, '2026-06-21');
INSERT INTO categories (name) VALUES ('Software'); -- category_id is filled in
SELECT customer_id, name, city FROM customers WHERE customer_id >= 9 ORDER BY customer_id;
SELECT * FROM categories ORDER BY category_id;+-------------+-------------+-------+
| customer_id | name | city |
+-------------+-------------+-------+
| 9 | Rina Begum | Dhaka |
| 10 | Arif Mollah | NULL |
+-------------+-------------+-------+
+-------------+-------------+
| category_id | name |
+-------------+-------------+
| 1 | Books |
| 2 | Electronics |
| 3 | Courses |
| 4 | Accessories |
| 5 | Furniture |
| 6 | Software |
+-------------+-------------+Columns you leave out get their DEFAULT, or NULL if they have none. The new category got id 6 automatically because category_id is an INTEGER PRIMARY KEY. Without a column list you must give every column, in table order, and a mismatch fails:
INSERT INTO customers VALUES (11, 'Joy Saha', 'joy@example.com', '2026-06-22');Error: table customers has 5 columns but 4 values were suppliedRETURNING: get back what you inserted
Your program usually needs the id the database just created. RETURNING (SQLite 3.35+, PostgreSQL) hands back columns of the new rows, as if the INSERT were also a SELECT:
INSERT INTO products (name, category_id, price, stock)
VALUES ('Data Science Handbook', 1, 1800, 20)
RETURNING product_id, name, price;+------------+-----------------------+-------+
| product_id | name | price |
+------------+-----------------------+-------+
| 12 | Data Science Handbook | 1800 |
+------------+-----------------------+-------+MySQL has no RETURNING; after an insert into an AUTO_INCREMENT table, SELECT LAST_INSERT_ID(); gives the new id. In Python, cursor.lastrowid gives it with sqlite3 and the MySQL drivers; with PostgreSQL, use RETURNING.
INSERT … SELECT: rows from a query
Instead of VALUES, an INSERT can take the result of a SELECT. This is how you build a work list, a snapshot or a training table from existing data in one statement. Here, a restock list for products with fewer than 10 in stock:
CREATE TABLE restock_requests (
product_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
order_qty INTEGER NOT NULL,
FOREIGN KEY (product_id) REFERENCES products (product_id)
);
INSERT INTO restock_requests (product_id, name, order_qty)
SELECT product_id, name, 20 - stock
FROM products
WHERE stock < 10;
SELECT * FROM restock_requests ORDER BY product_id;+------------+-----------------------------+-----------+
| product_id | name | order_qty |
+------------+-----------------------------+-----------+
| 5 | 27-inch Monitor | 15 |
| 9 | USB-C Hub | 20 |
| 10 | Noise-Cancelling Headphones | 12 |
+------------+-----------------------------+-----------+The courses (stock NULL) are not in the list: NULL < 10 is unknown, not true, so WHERE drops them, which is what we want here.
UPDATE: changing rows
UPDATE sets new values for every row that matches the WHERE. The new value can be an expression using the old one:
UPDATE products
SET stock = stock + 20, price = 2100
WHERE product_id = 9;
SELECT product_id, name, price, stock FROM products WHERE product_id = 9;+------------+-----------+-------+-------+
| product_id | name | price | stock |
+------------+-----------+-------+-------+
| 9 | USB-C Hub | 2100 | 20 |
+------------+-----------+-------+-------+Constraints apply to updates too. A rule that blocks a bad INSERT also blocks an UPDATE that would create the same bad value:
UPDATE orders SET status = 'returned' WHERE order_id = 14;Error: CHECK constraint failed: status IN ('pending', 'shipped', 'delivered', 'cancelled')DELETE: removing rows
DELETE FROM … WHERE … removes the matching rows. Foreign keys protect related data: you cannot delete a customer who still has orders.
DELETE FROM customers WHERE customer_id = 1;Error: FOREIGN KEY constraint failed-- PostgreSQL
DELETE FROM customers WHERE customer_id = 1;Error: update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders"Customer 10, who has never ordered, can go:
DELETE FROM customers WHERE customer_id = 10;
SELECT COUNT(*) AS customers FROM customers;+-----------+
| customers |
+-----------+
| 9 |
+-----------+One statement is all or nothing. If any row in a multi-row INSERT breaks a rule, none of the rows are stored:
INSERT INTO orders (order_id, customer_id, order_date, status)
VALUES (15, 2, '2026-06-15', 'pending'),
(16, 3, '2026-06-16', 'returned');Error: CHECK constraint failed: status IN ('pending', 'shipped', 'delivered', 'cancelled')SELECT COUNT(*) AS orders FROM orders;+--------+
| orders |
+--------+
| 14 |
+--------+The safe routine for UPDATE and DELETE
The classic disaster is an UPDATE or DELETE whose WHERE is missing or wrong. There is no undo button once it is committed. Professionals follow the same steps every time:
- SELECT first. Write the
WHEREas aSELECTand look at the rows (or at leastCOUNT(*)) it matches. - Open a transaction with
BEGIN, then run the change. - Check how many rows changed (
changes()in SQLite,ROW_COUNT()in MySQL, the "UPDATE 3" message in PostgreSQL,cursor.rowcountin Python) and look at the result. COMMITif it is right,ROLLBACKif it is not.- Before big changes, take a backup.
Watch the transaction save us from a forgotten WHERE:
BEGIN;
UPDATE products SET stock = 0; -- oops: meant WHERE product_id = 10
SELECT changes() AS rows_changed;
ROLLBACK; -- 12 rows was not the plan: undo
SELECT product_id, stock FROM products WHERE product_id IN (1, 2, 3) ORDER BY product_id;+--------------+
| rows_changed |
+--------------+
| 12 |
+--------------+
+------------+-------+
| product_id | stock |
+------------+-------+
| 1 | 40 |
| 2 | 15 |
| 3 | 12 |
+------------+-------+Inside the transaction the damage was real, but nobody else could see it, and ROLLBACK put every row back. MySQL can also refuse such statements outright with safe-update mode (MySQL Workbench turns it on by default). It accepts an UPDATE/DELETE only if the WHERE uses a key column or the statement has a LIMIT:
-- MySQL
SET SQL_SAFE_UPDATES = 1;
UPDATE products SET stock = 0;Error: ERROR 1175: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. For backups, the sqlite3 shell has .backup shop-backup.db, MySQL has mysqldump and PostgreSQL has pg_dump. From Python, SQLite can copy a live database in one call:
import sqlite3
from sqlhelp import con
backup = sqlite3.connect("shop-backup.db")
con.backup(backup) # a full, consistent copy of shop.db
n = backup.execute("SELECT COUNT(*) FROM products").fetchone()[0]
print("products in backup:", n)
backup.close()products in backup: 12Upserts: insert, or update if it already exists
Data pipelines re-run. A supplier sends a stock file every morning; some products are new, most already exist. A plain INSERT fails on the first existing key, and "check then insert" in Python is slow and racy. An upsert does it in one statement: insert the row, and if its key already exists, update that row instead. Running it twice gives the same result, which is what makes a pipeline idempotent.
INSERT INTO products (product_id, name, category_id, price, stock)
VALUES (9, 'USB-C Hub', 4, 2200, 30),
(13, 'Webcam', 2, 3500, 10)
ON CONFLICT (product_id) DO UPDATE SET stock = excluded.stock
RETURNING product_id, name, price, stock;+------------+-----------+-------+-------+
| product_id | name | price | stock |
+------------+-----------+-------+-------+
| 9 | USB-C Hub | 2100 | 30 |
| 13 | Webcam | 3500 | 10 |
+------------+-----------+-------+-------+Product 9 existed, so only its stock changed (its price stayed 2100); product 13 was new, so it was inserted. excluded means "the row you tried to insert". To skip rows that already exist instead, write ON CONFLICT DO NOTHING. PostgreSQL uses the same syntax; MySQL has its own. (The servers start from the original shop data, so there the hub's price is still 2200.)
-- PostgreSQL
INSERT INTO products (product_id, name, category_id, price, stock)
VALUES (9, 'USB-C Hub', 4, 2200, 30),
(13, 'Webcam', 2, 3500, 10)
ON CONFLICT (product_id) DO UPDATE SET stock = EXCLUDED.stock
RETURNING product_id, name, price, stock;+------------+-----------+-------+-------+
| product_id | name | price | stock |
+------------+-----------+-------+-------+
| 9 | USB-C Hub | 2200 | 30 |
| 13 | Webcam | 3500 | 10 |
+------------+-----------+-------+-------+-- MySQL (8.0.19+: name the new row with AS)
INSERT INTO products (product_id, name, category_id, price, stock)
VALUES (9, 'USB-C Hub', 4, 2200, 30),
(13, 'Webcam', 2, 3500, 10) AS new
ON DUPLICATE KEY UPDATE stock = new.stock;
SELECT product_id, name, price, stock FROM products WHERE product_id IN (9, 13) ORDER BY product_id;+------------+-----------+-------+-------+
| product_id | name | price | stock |
+------------+-----------+-------+-------+
| 9 | USB-C Hub | 2200 | 30 |
| 13 | Webcam | 3500 | 10 |
+------------+-----------+-------+-------+Older MySQL code writes stock = VALUES(stock), which still works but is deprecated. MySQL's version reacts to any unique key, not one you name.
The REPLACE trap
SQLite's INSERT OR REPLACE (and MySQL's REPLACE) look like upserts but are not: on a conflict they delete the old row and insert the new one. Every column you did not supply is lost:
INSERT OR REPLACE INTO products (product_id, name, price) VALUES (4, 'Wireless Mouse', 950);
SELECT product_id, name, category_id, price, stock FROM products WHERE product_id = 4;+------------+----------------+-------------+-------+-------+
| product_id | name | category_id | price | stock |
+------------+----------------+-------------+-------+-------+
| 4 | Wireless Mouse | NULL | 950 | NULL |
+------------+----------------+-------------+-------+-------+The mouse lost its category and its stock count. Use ON CONFLICT … DO UPDATE and name exactly the columns to change. Let us put the shop back the way it was before we continue:
from sqlhelp import con, reset
con.execute("DROP TABLE IF EXISTS restock_requests") # our own table points at products
reset() # shop tables back to their starting rows
print(con.execute("SELECT category_id, stock FROM products WHERE product_id = 4").fetchone())(2, 60)Writing data from Python
Real inserts come from programs: an import script, an API handler, a model logging its predictions. Use ? placeholders and pass values separately, never paste values into the SQL string (that is how SQL injection happens; the advanced tutorial shows it live). executemany runs one statement for a whole list, and nothing is saved until commit():
from sqlhelp import con, show
new_customers = [
(9, "Rina Begum", "rina@example.com", "Dhaka", "2026-06-20"),
(10, "Arif Mollah", "arif@example.com", "Barishal", "2026-06-21"),
]
con.executemany(
"INSERT INTO customers (customer_id, name, email, city, joined_on) VALUES (?, ?, ?, ?, ?)",
new_customers,
)
cur = con.execute("UPDATE products SET price = price + ? WHERE category_id = ?", (100, 4))
print("rows updated:", cur.rowcount)
con.commit() # make the changes permanent
show("SELECT customer_id, name, city FROM customers WHERE customer_id >= 9 ORDER BY customer_id")rows updated: 2
+-------------+-------------+----------+
| customer_id | name | city |
+-------------+-------------+----------+
| 9 | Rina Begum | Dhaka |
| 10 | Arif Mollah | Barishal |
+-------------+-------------+----------+If something fails halfway, call con.rollback() instead of commit(), and the half-done work disappears. Forgetting commit() is the most common beginner bug: the program "works", but the rows are gone when it exits, and meanwhile the open transaction can lock other programs out of the file.
Where this shows up in real systems
- Prediction logs: every model call is an
INSERTof input, output, model version and latency, which you later query to monitor drift and cost. - Embedding and feature caches: keyed by a document id or content hash and written with an upsert, so re-processing a document updates its row instead of duplicating it.
- Data fixes: a one-off
UPDATEto correct mislabelled rows, run with the SELECT-first, transaction-wrapped routine and reviewed by a teammate.
Common mistakes
- UPDATE or DELETE without a WHERE. Every row changes. Fix: write the
SELECTfirst, wrap the change inBEGIN…COMMIT, check the row count; in MySQL useSQL_SAFE_UPDATES. - INSERT without a column list. It breaks, or worse silently swaps values, when the table changes. Fix:
INSERT INTO customers (customer_id, name, email, city, joined_on) VALUES (…). - Using REPLACE as an upsert. It deletes and re-inserts, wiping columns you did not give (and in MySQL can fire delete cascades). Fix:
ON CONFLICT (key) DO UPDATE SET col = excluded.color MySQL'sON DUPLICATE KEY UPDATE. - Forgetting
commit()in Python. Changes vanish and the database stays locked. Fix:con.commit()after the writes, orwith con:, which commits on success and rolls back on an error. - Building SQL with f-strings. A name like
O'Brienbreaks the query and a malicious one rewrites it. Fix: placeholders,con.execute("… WHERE email = ?", (email,)).
Try it yourself
These build on each other; run them in order.
- Easy (Create): Add a category
'Software'with id 6 and a product'PyCharm Licence'in it, price 9000, stock NULL. Show the new product. - Medium (Update): The shop raises every Books price by 10%. First count the rows you will change, then do the update inside a transaction, check
changes(), commit, and show the books. - Hard (Upsert + Delete): A stock feed says product 3 now has 7 in stock and a new product 13,
'Graphics Tablet'(category 2, price 6500), has 4. Apply it with one upsert that also updates the price for existing products. Then delete the product you created in exercise 1, safely.
Answers
-- Easy
INSERT INTO categories (category_id, name) VALUES (6, 'Software');
INSERT INTO products (product_id, name, category_id, price, stock)
VALUES (12, 'PyCharm Licence', 6, 9000, NULL);
SELECT * FROM products WHERE product_id = 12;+------------+-----------------+-------------+-------+-------+
| product_id | name | category_id | price | stock |
+------------+-----------------+-------------+-------+-------+
| 12 | PyCharm Licence | 6 | 9000 | NULL |
+------------+-----------------+-------------+-------+-------+-- Medium
SELECT COUNT(*) AS books FROM products WHERE category_id = 1; -- expect 2
BEGIN;
UPDATE products SET price = price * 110 / 100 WHERE category_id = 1;
SELECT changes() AS rows_changed; -- 2: matches, so commit
COMMIT;
SELECT product_id, name, price FROM products WHERE category_id = 1 ORDER BY product_id;+-------+
| books |
+-------+
| 2 |
+-------+
+--------------+
| rows_changed |
+--------------+
| 2 |
+--------------+
+------------+---------------------------+-------+
| product_id | name | price |
+------------+---------------------------+-------+
| 1 | Python Crash Course | 1320 |
| 2 | Hands-On Machine Learning | 2750 |
+------------+---------------------------+-------+-- Hard
INSERT INTO products (product_id, name, category_id, price, stock)
VALUES (3, 'Mechanical Keyboard', 2, 4500, 7),
(13, 'Graphics Tablet', 2, 6500, 4)
ON CONFLICT (product_id) DO UPDATE SET stock = excluded.stock, price = excluded.price;
SELECT COUNT(*) AS to_delete FROM products WHERE product_id = 12; -- check first: 1 row
BEGIN;
DELETE FROM products WHERE product_id = 12;
SELECT changes() AS rows_deleted;
COMMIT;
SELECT product_id, name, price, stock FROM products WHERE product_id IN (3, 12, 13) ORDER BY product_id;+-----------+
| to_delete |
+-----------+
| 1 |
+-----------+
+--------------+
| rows_deleted |
+--------------+
| 1 |
+--------------+
+------------+---------------------+-------+-------+
| product_id | name | price | stock |
+------------+---------------------+-------+-------+
| 3 | Mechanical Keyboard | 4500 | 7 |
| 13 | Graphics Tablet | 6500 | 4 |
+------------+---------------------+-------+-------+Summary
INSERT INTO t (cols) VALUES (…), (…)adds rows; always list the columns.INSERT … SELECTcopies query results into a table.UPDATE t SET … WHERE …andDELETE FROM t WHERE …change and remove the matching rows; constraints and foreign keys guard both, and one statement is all or nothing.- Upserts make re-runs safe:
ON CONFLICT … DO UPDATE / DO NOTHING(SQLite, PostgreSQL),ON DUPLICATE KEY UPDATE(MySQL).REPLACEis not an upsert. RETURNING(SQLite 3.35+, PostgreSQL) gives back the rows you changed; MySQL usesLAST_INSERT_ID().- Safe routine: SELECT first,
BEGIN, change, check the row count,COMMITorROLLBACK, and back up before big changes. In Python: placeholders andcommit().
Next: Text, Number and Date Functions, CAST and COALESCE. Now that you can put data in, you will learn the functions that clean and reshape it inside a query: trimming and splitting text, rounding, date arithmetic, type conversion and safe handling of NULL and division by zero.