Adding Rows with INSERT

So far, your queries have only read the data: whatever you asked, the tables stayed the same. But a shop's data changes all the time. New customers join, prices go up and down, orders come in. In this lesson, you'll learn the statements that change data: INSERT adds rows, UPDATE changes them and DELETE deletes them.

A new row is added with INSERT INTO. This statement adds a new game to the shop:

INSERT INTO games (id, title, genre, price, released, age_rating)
VALUES (21, 'Snow Patrol', 'Action', 20, '2026-03-12', 12);

After INSERT INTO comes the table and, in parentheses, the columns you're filling. After VALUES come the values, in parentheses too and in the same order: the first value goes into the first column, the second value into the second column, and so on. Text and dates go in single quotes and numbers without them, just as in WHERE. And NULL means no value, as always.

An INSERT doesn't find any rows, so it has no result table. Instead, Results shows how many rows it changed: Rows changed: 1. To see the new row, query it in the same file, below the INSERT:

SELECT *
FROM games
WHERE id = 21;

Every Run starts with a fresh copy of the database. The INSERT adds Snow Patrol, the query below it shows the game, and when you click Run again, the database is back as it was at the start, and the INSERT adds the game once more. So in this course, you can't break anything: experiment as much as you like. It also means that the statements that change data and the queries that show the change must be in the same file. The Tables tab always shows the tables as they are at the start, before your statements run. (In a real database, a change stays for good. That's why this lesson also teaches you to be careful.)

You can leave out the list of columns: INSERT INTO games VALUES (21, 'Snow Patrol', …); then needs a value for every column of the table, in the order in which the table has them. Programmers avoid that. The statement is harder to read (which number is the price, and which is the age?), and when the table gets a new column one day, it stops working. Always list the columns.

Typical mistake: values that don't match the columns. When there are more or fewer values than columns, SQLite says so, for example 4 values for 3 columns. Values in the wrong order are worse: with (price, age_rating) and VALUES (12, 20), the game costs $12 and is for ages 20 and up, and no error tells you. Check that each value is under its column.