INSERT, UPDATE, DELETE: changing data

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;

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;

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;

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);

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;

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;

name | price
Notebook | 3.52
Pen set | 8.69

UPDATE products
SET price = 99.9,
    stock = stock + 10
WHERE name = 'Headphones';

SELECT name, price, stock
FROM products
WHERE name = 'Headphones';

name | price | stock
Headphones | 99.9 | 50

UPDATE students
SET grade = grade + 1;

SELECT first_name, grade
FROM students
ORDER BY id
LIMIT 3;

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;

orders_left | first_order
8 | 2025-03-05

-- no WHERE: every row is deleted
DELETE FROM enrollments;

SELECT COUNT(*) AS rows_left
FROM enrollments;

rows_left
0

1. 1
   SELECT first

   Write SELECT * with the same WHERE and look at which rows will be affected.

2. 2
   Count the rows

   Check with COUNT(*) that the number is what you expect.

3. 3
   Then change

   Replace SELECT * with DELETE or UPDATE ... SET, leaving the WHERE untouched.

4. 4
   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.

-- 1) INSERT the new product

-- 2) check the result
SELECT name, price, stock
FROM products
WHERE category = 'Electronics'
ORDER BY price DESC;

name | price | stock
Laptop | 1450 | 8
Smartphone | 899.99 | 15
Tablet | 520 | 10
Monitor | 310 | 0
Headphones | 120.5 | 40

-- 1) UPDATE the prices (don't forget WHERE!)

-- 2) check the result
SELECT name, price
FROM products
WHERE category = 'Stationery'
ORDER BY name;

name | price
Notebook | 2.56
Pen set | 6.32