mindixx
mindixx

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.

Back to the map