Changing Data Safely in SQL: INSERT, UPDATE, DELETE, Transactions

Every lesson so far has only read data. Reading is forgiving: a wrong SELECT gives you a wrong answer and nothing else happens. Writing is not forgiving in the same way — an UPDATE without a WHERE clause changes every row in the table, and there is no undo button once it is committed.

This lesson is therefore as much about habits as about syntax: check with a SELECT first, wrap risky work in a transaction, and let the table itself refuse data that cannot be right. The playground makes all of this safe to practise, because every run starts from a brand-new empty database that is thrown away afterwards — nothing you do here can damage anything.

INSERT

You met INSERT in lesson 1. Three forms are worth knowing properly.

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT, deposit INTEGER);

-- One row, naming the columns.
INSERT INTO tools (name, shelf, deposit) VALUES ('hedge trimmer', 'garden', 950);

-- Several rows in one statement.
INSERT INTO tools (name, shelf, deposit) VALUES
  ('cordless drill', 'power', 1200),
  ('wheelbarrow', 'garden', 500);

-- Rows copied from a query, with no VALUES at all.
CREATE TABLE garden_tools (id INTEGER PRIMARY KEY, name TEXT, deposit INTEGER);
INSERT INTO garden_tools (name, deposit)
SELECT name, deposit FROM tools WHERE shelf = 'garden';

SELECT * FROM garden_tools;

id | name          | deposit
---+---------------+--------
 1 | hedge trimmer |     950
 2 | wheelbarrow   |     500
(2 rows)

The column list is optional and you should write it anyway. Without it, INSERT INTO tools VALUES (...) depends on the physical order of the columns, so the day someone adds a column to the table every one of those statements starts putting values in the wrong place — or fails, if you are lucky.

INSERT ... SELECT is how data is copied, archived and summarised into another table. Anything a SELECT can produce, an INSERT can store.

UPDATE

UPDATE sets columns on the rows a WHERE clause picks out. The SET list can name several columns and can refer to the column's current value.

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT, deposit INTEGER);
INSERT INTO tools (name, shelf, deposit) VALUES
  ('hedge trimmer', 'garden', 950),
  ('cordless drill', 'power', 1200),
  ('wheelbarrow', 'garden', 500),
  ('tile cutter', 'power', 1500);

-- Step 1: see exactly which rows are about to change.
SELECT id, name, deposit FROM tools WHERE shelf = 'power';

-- Step 2: change them.
UPDATE tools SET deposit = deposit + 100 WHERE shelf = 'power';

-- Step 3: check.
SELECT id, name, deposit FROM tools ORDER BY id;

id | name           | deposit
---+----------------+--------
 2 | cordless drill |    1200
 4 | tile cutter    |    1500
(2 rows)

id | name           | deposit
---+----------------+--------
 1 | hedge trimmer  |     950
 2 | cordless drill |    1300
 3 | wheelbarrow    |     500
 4 | tile cutter    |    1600
(4 rows)

That three-step rhythm — SELECT with the same WHERE, then the UPDATE, then SELECT again — costs seconds and is the single habit that prevents most data accidents. If step 1 shows more rows than you expected, you have just saved yourself.

SQLite can also tell you what it changed as it changes it:

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT, deposit INTEGER);
INSERT INTO tools (name, shelf, deposit) VALUES
  ('hedge trimmer', 'garden', 950),
  ('cordless drill', 'power', 1200),
  ('tile cutter', 'power', 1500);

UPDATE tools SET deposit = deposit + 100 WHERE shelf = 'power'
RETURNING id, name, deposit;

SELECT changes() AS rows_changed_by_last_statement;

id | name           | deposit
---+----------------+--------
 2 | cordless drill |    1300
 3 | tile cutter    |    1600
(2 rows)

rows_changed_by_last_statement
------------------------------
                             2
(1 row)

RETURNING arrived in SQLite 3.35 and in PostgreSQL long before that; MySQL has no equivalent. changes() is SQLite's own function for "how many rows did that touch", which PostgreSQL and MySQL expose through their client libraries instead. Both are useful sanity checks: a number you did not expect means a WHERE clause you did not mean.

DELETE

DELETE FROM table WHERE ... removes whole rows. There is no way to delete part of a row; setting a column to NULL is an UPDATE.

CREATE TABLE loans (id INTEGER PRIMARY KEY, member_id INTEGER, taken_on TEXT, returned_on TEXT);
INSERT INTO loans (member_id, taken_on, returned_on) VALUES
  (1, '2026-03-04', '2026-03-11'),
  (1, '2026-03-21', NULL),
  (2, '2026-03-19', '2026-03-26'),
  (3, '2026-04-02', NULL);

SELECT count(*) AS before_delete FROM loans;

DELETE FROM loans WHERE returned_on IS NOT NULL;

SELECT id, member_id, taken_on FROM loans ORDER BY id;
SELECT count(*) AS after_delete FROM loans;

before_delete
-------------
            4
(1 row)

id | member_id | taken_on
---+-----------+-----------
 2 |         1 | 2026-03-21
 4 |         3 | 2026-04-02
(2 rows)

after_delete
------------
           2
(1 row)

Two rows gone, two left. Note the WHERE clause used IS NOT NULL rather than <> NULL, for the reason lesson 2 laboured: <> NULL matches nothing, so that DELETE would have deleted nothing and looked like it worked.

SQLite has no TRUNCATE TABLE. DELETE FROM loans with no WHERE empties the table, and it is worth writing DELETE FROM loans WHERE 1 = 1 when you really mean it, purely so that a WHERE clause is visibly present and nobody — including you in six months — reads it as an accident.

Transactions

A transaction groups statements so that either all of them happen or none of them do. BEGIN starts one, COMMIT makes it permanent, ROLLBACK throws the whole thing away.

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, deposit INTEGER);
INSERT INTO tools (name, deposit) VALUES ('hedge trimmer', 950), ('cordless drill', 1200);

BEGIN;
UPDATE tools SET deposit = 0;
SELECT name, deposit AS inside_the_transaction FROM tools ORDER BY id;
ROLLBACK;

SELECT name, deposit AS after_rollback FROM tools ORDER BY id;

name           | inside_the_transaction
---------------+-----------------------
hedge trimmer  |                      0
cordless drill |                      0
(2 rows)

name           | after_rollback
---------------+---------------
hedge trimmer  |            950
cordless drill |           1200
(2 rows)

The UPDATE really did happen — the query inside the transaction proves it — and then ROLLBACK put the table back exactly as it was. Swap that word for COMMIT and the zeroes stay.

This is what makes a multi-step change safe. A loan return that has to update two tables should not be able to half-happen:

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, on_loan INTEGER DEFAULT 0);
CREATE TABLE loans (id INTEGER PRIMARY KEY, tool_id INTEGER, returned_on TEXT);
INSERT INTO tools (id, name, on_loan) VALUES (10, 'hedge trimmer', 1);
INSERT INTO loans (id, tool_id, returned_on) VALUES (1, 10, NULL);

BEGIN;
UPDATE loans SET returned_on = '2026-04-14' WHERE id = 1;
UPDATE tools SET on_loan = 0 WHERE id = 10;
COMMIT;

SELECT t.name, t.on_loan, l.returned_on FROM tools AS t JOIN loans AS l ON l.tool_id = t.id;

name          | on_loan | returned_on
--------------+---------+------------
hedge trimmer |       0 | 2026-04-14
(1 row)

Both updates or neither. Without the transaction, a failure between them would leave a returned loan against a tool still marked as out.

A few practical points. SQLite treats any statement outside a transaction as a transaction of its own, so a single UPDATE is already all-or-nothing. SAVEPOINT name and ROLLBACK TO name give you a partial undo inside a longer transaction. And in this playground the whole run is thrown away at the end regardless, so transactions here are for learning the shape rather than for protecting anything.

Constraints: letting the table refuse bad data

A constraint is a rule the table enforces on every write, so a bug in one place cannot quietly corrupt the data for everyone else.

• PRIMARY KEY — this column identifies the row. Values must be unique.
• NOT NULL — this column must always have a value.
• UNIQUE — no two rows may share this value.
• CHECK (condition) — the condition must hold for every row.
• DEFAULT value — what to store when an INSERT does not mention the column.
• FOREIGN KEY — this column's values must exist in another table's key.

CREATE TABLE members (
  id       INTEGER PRIMARY KEY,
  name     TEXT    NOT NULL,
  email    TEXT    UNIQUE,
  joined   TEXT    DEFAULT '2026-01-01',
  credits  INTEGER NOT NULL DEFAULT 0 CHECK (credits >= 0)
);

INSERT INTO members (name, email) VALUES ('Asha', 'asha@example.invalid');
INSERT INTO members (name, email, credits) VALUES ('Bruno', 'bruno@example.invalid', 5);

SELECT * FROM members;

INSERT INTO members (name, email, credits) VALUES ('Cleo', 'cleo@example.invalid', -2);

id | name  | email                 | joined     | credits
---+-------+-----------------------+------------+--------
 1 | Asha  | asha@example.invalid  | 2026-01-01 |       0
 2 | Bruno | bruno@example.invalid | 2026-01-01 |       5
(2 rows)

Error in the statement on line 14: CHECK constraint failed: credits >= 0

The first two rows went in, took their defaults, and the third was refused with the rule named in the error. That is the whole point: the error happened at the moment of the bad write, not three weeks later in a report nobody trusts. The email addresses use example.invalid, a domain reserved for exactly this kind of made-up example.

Two SQLite details to carry with you. UNIQUE allows any number of NULLs, because two unknown values are not known to be equal — that is standard SQL, not an SQLite choice. But a PRIMARY KEY on a column that is not INTEGER PRIMARY KEY does allow NULLs in SQLite, which no other database permits:

CREATE TABLE codes (code TEXT PRIMARY KEY, label TEXT);
INSERT INTO codes (code, label) VALUES ('A1', 'first'), (NULL, 'oops'), (NULL, 'again');
SELECT code, label FROM codes;

code | label
-----+------
A1   | first
NULL | oops
NULL | again
(3 rows)

Two NULL primary keys in a table whose key is meant to identify rows. This is a documented, long-standing SQLite quirk kept for backwards compatibility; PostgreSQL and MySQL both reject it. If you use a text primary key in SQLite, write code TEXT PRIMARY KEY NOT NULL and say it twice.

FOREIGN KEY, and the PRAGMA you must not forget

A foreign key says "this column points at that table's key". Declare one and the database can refuse a loan against a member who does not exist. In SQLite there is a catch, and it is a big one.

CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE loans (
  id        INTEGER PRIMARY KEY,
  member_id INTEGER NOT NULL REFERENCES members(id)
);

INSERT INTO members (id, name) VALUES (1, 'Asha');
INSERT INTO loans (member_id) VALUES (1), (99);

SELECT * FROM loans;

id | member_id
---+----------
 1 |         1
 2 |        99
(2 rows)

There is no member 99, the table says REFERENCES members(id), and SQLite stored the row anyway. SQLite does not enforce foreign keys unless you turn them on, per connection, with PRAGMA foreign_keys = ON. It is off by default for backwards compatibility, and it is the most consequential difference between SQLite and every other database in this lesson: a schema that looks protected is not.

With the pragma, the same script behaves the way you expected all along:

PRAGMA foreign_keys = ON;

CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE loans (
  id        INTEGER PRIMARY KEY,
  member_id INTEGER NOT NULL REFERENCES members(id)
);

INSERT INTO members (id, name) VALUES (1, 'Asha');
INSERT INTO loans (member_id) VALUES (1);

SELECT * FROM loans;

INSERT INTO loans (member_id) VALUES (99);

id | member_id
---+----------
 1 |         1
(1 row)

Error in the statement on line 14: FOREIGN KEY constraint failed

Put that PRAGMA line at the top of every SQLite script that writes data, including the ones in this playground. PostgreSQL and MySQL (with the InnoDB engine) enforce foreign keys always and have no such switch, so this is one line you add going to SQLite and delete coming from it.

A foreign key also controls what happens when the row it points at is deleted. ON DELETE CASCADE deletes the pointing rows too; ON DELETE SET NULL blanks the column; the default is to refuse the delete. Choosing deliberately is worth a minute, because CASCADE is convenient right up until it removes more than you meant.

A worked example

A whole small change, done safely: add a member, record a loan, and correct a deposit, all inside one transaction with checks either side.

PRAGMA foreign_keys = ON;

CREATE TABLE members (
  id      INTEGER PRIMARY KEY,
  name    TEXT NOT NULL,
  credits INTEGER NOT NULL DEFAULT 2 CHECK (credits >= 0)
);

CREATE TABLE tools (
  id      INTEGER PRIMARY KEY,
  name    TEXT NOT NULL UNIQUE,
  deposit INTEGER NOT NULL CHECK (deposit >= 0)
);

CREATE TABLE loans (
  id        INTEGER PRIMARY KEY,
  member_id INTEGER NOT NULL REFERENCES members(id),
  tool_id   INTEGER NOT NULL REFERENCES tools(id),
  taken_on  TEXT NOT NULL
);

INSERT INTO members (id, name, credits) VALUES (1, 'Asha', 2), (2, 'Bruno', 1);
INSERT INTO tools (id, name, deposit) VALUES (10, 'hedge trimmer', 950), (11, 'cordless drill', 1200);

BEGIN;
INSERT INTO members (id, name) VALUES (3, 'Cleo');
INSERT INTO loans (member_id, tool_id, taken_on) VALUES (3, 11, '2026-04-14');
UPDATE members SET credits = credits - 1 WHERE id = 3;
UPDATE tools SET deposit = 1250 WHERE id = 11;
COMMIT;

SELECT m.name, m.credits, t.name AS tool, t.deposit, l.taken_on
FROM loans AS l
JOIN members AS m ON m.id = l.member_id
JOIN tools AS t ON t.id = l.tool_id;

SELECT id, name, credits FROM members ORDER BY id;

name | credits | tool           | deposit | taken_on
-----+---------+----------------+---------+-----------
Cleo |       1 | cordless drill |    1250 | 2026-04-14
(1 row)

id | name  | credits
---+-------+--------
 1 | Asha  |       2
 2 | Bruno |       1
 3 | Cleo  |       1
(3 rows)

Cleo took her credits default of 2 and then the UPDATE spent one of them. Every write in that transaction had to satisfy the constraints: a name, a non-negative credit balance, a tool name nobody else has, and member and tool ids that exist. If any single one had failed, the COMMIT would never have been reached and the whole change would have vanished.

Common mistakes

UPDATE with no WHERE

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, deposit INTEGER);
INSERT INTO tools (name, deposit) VALUES
  ('hedge trimmer', 950), ('cordless drill', 1200), ('wheelbarrow', 500);

UPDATE tools SET deposit = 1250;

SELECT id, name, deposit FROM tools ORDER BY id;

id | name           | deposit
---+----------------+--------
 1 | hedge trimmer  |    1250
 2 | cordless drill |    1250
 3 | wheelbarrow    |    1250
(3 rows)

One deposit needed correcting and all three changed. There is no error, no warning and no confirmation prompt — an UPDATE with no WHERE is a valid statement that means "every row", and outside a transaction it is already committed by the time you read the result. This is the mistake to be slightly afraid of. Run the SELECT first, every time.

DELETE with a condition that matches nothing

CREATE TABLE loans (id INTEGER PRIMARY KEY, member_id INTEGER, returned_on TEXT);
INSERT INTO loans (member_id, returned_on) VALUES (1, '2026-03-11'), (2, NULL);

DELETE FROM loans WHERE returned_on = NULL;

SELECT count(*) AS still_here FROM loans;

still_here
----------
         2
(1 row)

Nothing was deleted and nothing complained. = NULL is never true, so the statement did exactly nothing — the opposite failure to the one above, and just as quiet. changes() would have reported 0 and given the game away.

Inserting values in the wrong column order

CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT, deposit INTEGER);
INSERT INTO tools VALUES (1, 'garden', 'hedge trimmer', 950);
SELECT id, name, shelf, deposit FROM tools;

id | name   | shelf         | deposit
---+--------+---------------+--------
 1 | garden | hedge trimmer |     950
(1 row)

The shelf ended up in the name column and the name in the shelf column. Both are text, so nothing was violated and nothing was reported; the row is simply wrong. Naming the columns in the INSERT makes this impossible, which is why the column list is worth the typing.

Assuming a declared type or a foreign key is protecting you

CREATE TABLE loans (
  id        INTEGER PRIMARY KEY,
  member_id INTEGER REFERENCES members(id),
  taken_on  TEXT
);

INSERT INTO loans (member_id, taken_on) VALUES ('not a number', 42);

SELECT member_id, typeof(member_id) AS member_id_type, taken_on, typeof(taken_on) AS taken_on_type
FROM loans;

member_id    | member_id_type | taken_on | taken_on_type
-------------+----------------+----------+--------------
not a number | text           | 42       | text
(1 row)

A sentence in an INTEGER column, a number in a TEXT column, and a reference to a table that does not even exist — SQLite accepted all of it. Read the two typeof columns carefully, because they show two different things happening. 'not a number' stayed text, because a declared type in SQLite is only an affinity and a value that cannot be converted is kept as it is. The 42, on the other hand, was converted, into the text '42', because the column's affinity is TEXT and a number can be written as one. Neither is an error.

On top of that, the foreign key was never checked, because the pragma was off. PostgreSQL and MySQL would have refused this row several different ways over. Turn the pragma on, consider STRICT tables, and add NOT NULL and CHECK where the data has real rules.

Next steps

You can now write to a database with the safety rails up. One capability is still missing: looking at other rows without collapsing them — ranking within a group, or a running total down a column. Window functions are the last lesson in this track and the one that makes SQL feel powerful. Meanwhile, take the worked example into the SQL playground, change COMMIT to ROLLBACK, and watch the whole change disappear.