Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 33 ยท Changing Data

INSERT, UPDATE and DELETE

Change data in SQLite: multi-row INSERT, INSERT ... SELECT, UPDATE with expressions and UPDATE FROM, DELETE patterns, RETURNING, and counting rows with changes().

Querying is only half of a database's job. The other half is changing data: adding rows, correcting them and removing them. SQLite uses the standard three statements, INSERT, UPDATE and DELETE, and adds a few conveniences (such as UPDATE ... FROM, RETURNING and the changes() function) while leaving out a few things you might expect from other databases.

Every example below runs against a fresh in-memory database, so you can break things freely: pressing Run again starts from scratch. If you want the portable, database-neutral introduction first, see the SQL tutorial pages for INSERT, UPDATE and DELETE. Here we focus on SQLite's behavior.

Three statements, one habit

INSERT Add new rows.
UPDATE Change existing rows.
DELETE Remove rows.

Always check the WHERE clause before you press Run.

1 / 2

INSERT: one row, many rows, defaults

The basic form names the columns and gives a list of values. Name the columns every time: the statement keeps working if someone later adds a column to the table. A single INSERT can add several rows with a comma-separated list of value tuples, which is far faster than separate statements.

Columns you leave out get their DEFAULT value, or NULL if there is none. INSERT INTO t DEFAULT VALUES inserts one row made entirely of defaults. SQLite does not accept the keyword DEFAULT inside a VALUES list (other databases do), so to use a default, simply leave that column out of the column list.

SQL Inserting rows

Press Run (or Ctrl + Enter) to see the result here.

The first insert reports one row added with the new id 1. The multi-row insert reports 3 rows added, and last_insert_rowid() is 4, the id of the last of them. The final listing shows that the DEFAULT VALUES row became unnamed/cat, and that Zed received the default species cat because we left that column out.

last_insert_rowid() returns the rowid of the most recent successful insert on the same connection, so it is safe even when other programs write to the database at the same time. For tables with an INTEGER PRIMARY KEY that value is the primary key; the tables and rowid lesson explains why.

INSERT ... SELECT and CREATE TABLE AS

Instead of VALUES, an insert can take its rows from a query. That is the standard way to copy, archive or reshape data. CREATE TABLE ... AS SELECT does the same while creating the table, though the new table carries no constraints, keys or defaults.

SQL Copying rows with a query

Press Run (or Ctrl + Enter) to see the result here.

Two rows are copied (Miso and Rex; Pip's NULL age fails the >= 3 test). cats ends up with Luna and Miso. When an insert might hit a duplicate key, SQLite offers OR IGNORE, OR REPLACE and a full upsert clause: those get their own lesson, UPSERT and RETURNING.

UPDATE: change rows that exist

UPDATE table SET column = expression, ... WHERE condition rewrites the matching rows. The right-hand side can use the row's current values, so age = age + 1 works. SQLite also supports row-value assignment, SET (a, b) = (1, 2), which is tidy when several columns change together.

The single most important rule: an UPDATE without WHERE changes every row. There is no confirmation and no undo outside a transaction. Write the WHERE first, and run it as a SELECT to preview which rows it will hit.

SQL Updating rows

Press Run (or Ctrl + Enter) to see the result here.

Two cats were updated. An update that matches nothing is not an error: it simply reports 0 changes, so check changes() when "nothing happened" would be a bug. After the careless last statement all 4 rows have age 99.

UPDATE ... FROM

When the new values live in another table, SQLite (3.33 and later) supports the UPDATE ... FROM form. List the source table after FROM and join it to the target in the WHERE clause:

SQL Updating from another table ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Only two products are updated: the row for product 999 matches nothing, and product 3 is not in the change list. The second statement shows the older style, a scalar subquery in WHERE, with RETURNING to print the changed rows (after the first update the skillet costs 30 and then 33.00). Inside the target table, refer to columns by the table name (products.id); the FROM table keeps its alias.

If your database might be older than 3.33, the portable alternative is a correlated subquery: SET price = (SELECT new_price FROM price_changes WHERE product_id = products.id) WHERE id IN (SELECT product_id FROM price_changes).

DELETE: remove rows

DELETE FROM table WHERE condition removes the matching rows. Like UPDATE, it deletes everything when you omit WHERE, and SQLite has an optimization that makes a bare DELETE FROM table fast.

Some patterns you will use all the time:

  • Delete by a list from another query: DELETE FROM a WHERE id IN (SELECT id FROM b).
  • Delete the oldest N rows: SQLite does not allow ORDER BY ... LIMIT on a DELETE or UPDATE unless it was compiled with a special option (this site's engine rejects it as a syntax error). Put the ordering in a subquery instead.
  • See what was deleted with RETURNING.
SQL Deleting rows ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Out of 26 products, three have no stock (ids 5, 14 and 23), which leaves 23. The second delete removes the three oldest (by added_on) of those. The last statement has no WHERE, so it deletes all 20 that remain.

SQLite does not enforce foreign keys unless you switch them on with PRAGMA foreign_keys = ON (see the foreign keys lesson). By default you can delete a parent row and leave children pointing at nothing, with no error.

Counting what changed

changes() returns the number of rows modified by the most recently completed INSERT, UPDATE or DELETE on this connection. Related functions:

Function Meaning
changes() Rows changed by the last statement (triggers are not counted)
total_changes() Rows changed since the connection opened, including trigger changes
last_insert_rowid() Rowid of the last inserted row

Your programming language's SQLite driver exposes the same numbers (for example cursor.rowcount in Python). The sqlite3 command-line shell can print the count after every statement:

TEXT
sqlite> .changes on
sqlite> DELETE FROM pets WHERE age > 10;
changes: 2   total_changes: 12

That block is read-only; it only works in the real shell, which is covered in Running SQLite.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which INSERT is not valid in SQLite?

    SQLite does not accept the DEFAULT keyword inside a VALUES list. Leave the column out of the column list to get its default.
  2. 2. What happens if an UPDATE matches no rows?

    An update that matches nothing is valid. Check changes() when you need to detect it.
  3. 3. What does UPDATE pets SET age = 99; do?

    Without WHERE the statement applies to all rows. Preview with SELECT first, and use a transaction when in doubt.
  4. 4. How do you delete the 3 oldest rows in a default SQLite build?

    DELETE ... ORDER BY ... LIMIT needs a special compile-time option. A subquery that picks the ids works everywhere.
  5. 5. Which statement updates a table using values from another table?

    SQLite 3.33 added UPDATE ... FROM with the join condition in WHERE. The JOIN-in-UPDATE and MERGE forms are not supported.

Summary

  • Name the columns in INSERT; use multi-row VALUES or INSERT ... SELECT to add many rows at once.
  • SQLite has no DEFAULT keyword inside VALUES; omit the column or use DEFAULT VALUES.
  • UPDATE supports expressions, row-value assignment and, since 3.33, UPDATE ... FROM.
  • UPDATE and DELETE without WHERE hit every row; ORDER BY ... LIMIT on them needs a subquery.
  • RETURNING hands back the affected rows; changes(), total_changes() and last_insert_rowid() report counts and ids.
  • Foreign keys are off by default, so deletes can orphan child rows.

Next: UPSERT and RETURNING, handling duplicate keys and getting generated values back.