Reference · module 6
Changing data
Everything this module explains, on one page.
INSERT
Add rows to a table safely.
- INSERT adds rows: INSERT INTO books (title, year) VALUES ('Dune', 1965);. The values go in the same order as the columns listed before them.
- Always list the columns. Columns you leave out get their default or NULL, and an id declared INTEGER PRIMARY KEY is filled in by the database automatically.
- One INSERT can add several rows, each in its own brackets: VALUES ('Emma', 1815), ('Solaris', 1961);. It is one statement, so either all the rows go in or none do.
The mistake you are about to make
INSERT INTO books VALUES ('Dune', 1965);
INSERT INTO books (title, year) VALUES ('Dune', 1965);
Leaving out the column list is shorter, and sometimes it even works. The values then follow the table's column order: add a column, and the same INSERT fails or writes to the wrong place.
UPDATE and DELETE
Change and remove rows without touching the ones you meant to keep.
- UPDATE changes existing rows: UPDATE products SET price = 9 WHERE id = 3;. SET can change several columns, separated by commas, and can use the old value: SET stock = stock - 1.
- DELETE removes rows: DELETE FROM orders WHERE paid = 0;. It deletes whole rows; to clear a single value, set it to NULL with UPDATE instead.
- Both apply to every row that matches WHERE — and without WHERE, to every row in the table. A habit that saves data: run the same WHERE with SELECT first and look at what it finds.
The mistake you are about to make
UPDATE users SET email = 'new@mail.com';
UPDATE users SET email = 'new@mail.com' WHERE id = 7;
In your head you have already picked the user, so the statement feels finished. The database does not know that: without WHERE every user in the table gets the new address.
CREATE TABLE
Create a table whose rules keep bad data out.
- CREATE TABLE makes a new table: CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT NOT NULL, created TEXT);. Each column gets a name, then a type, then optional rules.
- Common types are INTEGER, REAL for fractional numbers, and TEXT. SQLite is relaxed about types, but other databases are strict, so declare what you really mean to store.
- PRIMARY KEY marks the column that identifies each row, so its values are unique. NOT NULL refuses rows without a value: a note with no body fails instead of saving an empty record.
The mistake you are about to make
CREATE TABLE users (id INTEGER, email TEXT);
CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL);
The table exists and works, so the rules seem optional. Without them the database accepts two users with the same id and a user with no email — and you find the bug in the data.