So far we have only read data. But real applications change data every day: a new student joins the school, prices go up in the shop, cancelled orders are removed. SQL has three commands for this: INSERT, UPDATE and DELETE. Don't worry: every example here runs on a fresh copy of the practice database, so nothing gets broken for good.
INSERT: adding rows
INSERT INTO table (columns) VALUES (values) — the values go in the same order as the columns. We don't write the id column: in SQLite an INTEGER PRIMARY KEY column automatically gets the next number. You can put several statements in one block — the result of the last SELECT is shown.
INSERT INTO students (first_name, last_name, age, grade, city, email)
VALUES ('Nərmin', 'Səlimova', 15, 9, 'Bakı', 'narmin@example.com');
SELECT id, first_name, last_name, city
FROM students
WHERE id > 10
ORDER BY id;Expected output
id | first_name | last_name | city 11 | Fidan | Cəfərova | Naxçıvan 12 | Orxan | Babayev | Sumqayıt 13 | Nərmin | Səlimova | Bakı
To add several rows with one statement, separate the groups of values with commas:
INSERT INTO products (name, category, price, stock)
VALUES ('Calculator', 'Stationery', 18.5, 70),
('Board game', 'Games', 29.9, 12);
SELECT id, name, category, price, stock
FROM products
WHERE category IN ('Stationery', 'Games')
ORDER BY id;Expected output
id | name | category | price | stock 4 | Notebook | Stationery | 3.2 | 500 5 | Pen set | Stationery | 7.9 | 300 10 | Chess set | Games | 42 | 18 11 | Calculator | Stationery | 18.5 | 70 12 | Board game | Games | 29.9 | 12
Columns missing from the list get NULL (or the DEFAULT value defined for the table). The customer below has no sign-up date, so joined_on is NULL. But you can't leave out a column with a NOT NULL constraint — the DBMS rejects the statement.
INSERT INTO customers (name, city, country)
VALUES ('Aygün Qasımova', 'Quba', 'Azerbaijan');
SELECT id, name, city, joined_on
FROM customers
WHERE id >= 6
ORDER BY id;Expected output
id | name | city | joined_on 6 | Zəhra Hüseynli | Bakı | 2025-02-14 7 | Mehmet Kaya | Ankara | 2025-04-01 8 | Aygün Qasımova | Quba | NULL
-- last_name is NOT NULL, so this fails
INSERT INTO students (first_name, age)
VALUES ('Test', 10);Expected output
Error: NOT NULL constraint failed: students.last_name
INSERT can also be combined with SELECT: the new rows are then taken from another query's result. For example, let's enroll all 11th graders in course 7 (English B1). There is no score yet, so we write NULL.
INSERT INTO enrollments (student_id, course_id, score, enrolled_on)
SELECT id, 7, NULL, '2025-10-01'
FROM students
WHERE grade = 11
ORDER BY id;
SELECT e.student_id, s.first_name, e.score, e.enrolled_on
FROM enrollments AS e
JOIN students AS s ON s.id = e.student_id
WHERE e.course_id = 7
ORDER BY e.id;Expected output
student_id | first_name | score | enrolled_on 3 | Leyla | 90 | 2025-09-17 7 | Günay | 64 | 2025-09-20 4 | Elvin | NULL | 2025-10-01 8 | Tural | NULL | 2025-10-01 11 | Fidan | NULL | 2025-10-01
UPDATE: changing rows
UPDATE table SET column = value WHERE condition changes a column's value in the rows that match the condition. The new value can be calculated from the old one. Below we raise the prices of stationery by 10%.
UPDATE products
SET price = ROUND(price * 1.10, 2)
WHERE category = 'Stationery';
SELECT name, price
FROM products
WHERE category = 'Stationery'
ORDER BY id;Expected output
name | price Notebook | 3.52 Pen set | 8.69
Worked example
Problem
The Headphones now cost 99.90, and 10 more have arrived at the warehouse. Update the table.
Show solutionHide solution
Solution
SET can change several columns, separated by commas.The price is a fixed number:
price = 99.9; the stock is calculated from the old value: stock = stock + 10.WHERE name = 'Headphones' selects just one row. Result: price 99.9, stock 40 + 10 = 50.UPDATE products
SET price = 99.9,
stock = stock + 10
WHERE name = 'Headphones';
SELECT name, price, stock
FROM products
WHERE name = 'Headphones';Expected output
name | price | stock Headphones | 99.9 | 50
UPDATE students
SET grade = grade + 1;
SELECT first_name, grade
FROM students
ORDER BY id
LIMIT 3;Expected output
first_name | grade Aysel | 10 Murad | 11 Leyla | 9
DELETE: removing rows
DELETE FROM table WHERE condition removes the rows that match the condition. Below we delete the orders placed before March: 4 of the 12 orders are removed and 8 remain.
DELETE FROM orders
WHERE order_date < '2025-03-01';
SELECT COUNT(*) AS orders_left,
MIN(order_date) AS first_order
FROM orders;Expected output
orders_left | first_order 8 | 2025-03-05
-- no WHERE: every row is deleted
DELETE FROM enrollments;
SELECT COUNT(*) AS rows_left
FROM enrollments;Expected output
rows_left 0
SELECT first
Write
SELECT *with the sameWHEREand look at which rows will be affected.Count the rows
Check with
COUNT(*)that the number is what you expect.Then change
Replace
SELECT *withDELETEorUPDATE ... SET, leaving theWHEREuntouched.Important changes in a transaction
On a real database, make big changes inside a transaction so you can undo them if something goes wrong. You will learn this in the last lesson.
| Task | SQLite | MySQL | PostgreSQL | SQL Server |
|---|---|---|---|---|
| Delete all rows quickly | DELETE FROM t | TRUNCATE TABLE t | TRUNCATE TABLE t | TRUNCATE TABLE t |
| Id of the new row | LAST_INSERT_ROWID() | LAST_INSERT_ID() | RETURNING id | SCOPE_IDENTITY() |
| Update if it exists, otherwise insert | ON CONFLICT … DO UPDATE | ON DUPLICATE KEY UPDATE | ON CONFLICT … DO UPDATE | MERGE |
Exercise
A new product has arrived: Tablet, category Electronics, price 520, 10 in stock. Add it to products, then show the name, price and stock of all Electronics products, sorted by price from high to low.
-- 1) INSERT the new product
-- 2) check the result
SELECT name, price, stock
FROM products
WHERE category = 'Electronics'
ORDER BY price DESC;Expected output
name | price | stock Laptop | 1450 | 8 Smartphone | 899.99 | 15 Tablet | 520 | 10 Monitor | 310 | 0 Headphones | 120.5 | 40
Exercise
Stationery is 20% off: lower the price of the products in the Stationery category by 20% and round it to 2 decimal places. Then show the name and new price of those products, sorted by name.
-- 1) UPDATE the prices (don't forget WHERE!)
-- 2) check the result
SELECT name, price
FROM products
WHERE category = 'Stationery'
ORDER BY name;Expected output
name | price Notebook | 2.56 Pen set | 6.32
Remember
INSERT INTO t (columns) VALUES (...)adds a new row; always list the columns.- Columns you leave out get
NULLor theirDEFAULT; aNOT NULLcolumn can't be left out. UPDATE t SET column = value WHERE ...changes rows; the new value can be based on the old one.DELETE FROM t WHERE ...removes rows.UPDATEandDELETEwithoutWHEREaffect the whole table — run aSELECTwith the same condition first.