Table of Contents for

Using SQLite

The INSERT command is used to create new rows in the specified table. There are two meaningful versions of the command. The first version uses a VALUES clause to specify a list of values to insert:

INSERT INTO table_name (column_name [, ...]) VALUES (new_value [, ...]);

A table name is provided, along with a list of columns and a list of values. Both lists must have the same number of items. A single new row is created and each value is recorded into its respective column. The columns can be listed in any order, just as long as the list of columns and the list of values line up correctly. Any columns that are not listed will receive their default values:

INSERT INTO parts ( name, stock, status ) VALUES ( 'Widget', 17, 'IN STOCK' );

In this example, we attempt to insert a new row into a “parts” table. Note the use of single quotes for text literals.

Technically, the list of column names is optional. If no explicit list of columns is provided, the INSERT command will attempt to pair up values with the table’s full list of columns:

INSERT INTO table_name VALUES (new_value [, ...]);

The trick with this format is that the number and order of values must exactly match the number and order of columns in the table definition. That means it is impossible to use default values, even on INTEGER PRIMARY KEY columns. More often than not, this is not actually desirable. This format is also harder to maintain within application source code, since it must be meticulously updated if the table format changes. In general, it is recommended that you always explicitly list out the columns in an INSERT statement.

When bulk importing data, it is common to loop over data sets, calling INSERT over and over. Processing these statements one at a time can be fairly slow, since each command will update both the table and any relevant indexes, and then make sure the data is fully written out to physical disk before (finally!) starting the next INSERT. This is a fairly lengthly process, since it requires physical I/O.

To speed up bulk inserts, it is common to wrap groups of 1,000 to 10,000 INSERT statements into a single transaction. Grouping the statement together will substantially increase the overall speed of the inserts by delaying the physical I/O until the end of the transaction. See Transaction Control Language for more information on transactions.

The second version of INSERT allows you to define values by using a query statement. This is very similar to the CREATE TABLE...AS SELECT command, although the table must already exist. This is the only version of INSERT that can insert more than one row with a single command:

INSERT INTO table_name (column_name, [...]) SELECT query_statement;

This type of INSERT is most commonly used to bulk copy data from one table to another. This is a common operation when you need to update the definition of a table, but you don’t want to lose all the data that already exists in the database. The old table is renamed, the new table is defined, and the data is copied from the old table into the new table using an INSERT INTO...SELECT command. This form can also be used to populate temporary tables or copy data from one attached database to another.

As with the VALUES version of INSERT, the column list is technically optional but, for all the same reasons, it is still recommended that you provide an explicit column list.

All versions of the INSERT command also support an optional conflict resolution clause. This conflict clause determines what should be done if the results of the INSERT would violate a database constraint. The most common example is INSERT OR REPLACE, which comes into play when the INSERT would, as executed, cause a UNIQUE constraint violation. If the REPLACE conflict resolution is present, any existing row that would cause a UNIQUE constraint violation is first deleted, and then the INSERT is allowed to continue. This specific usage pattern is so common that the whole INSERT OR REPLACE phrase can be replaced by just REPLACE. For example, REPLACE INTO table_name....

See INSERT and UPDATE in Appendix C for more information on the details of conflict resolution.

The UPDATE command is used to assign new values to one or more columns of existing rows in a table. The command can update more than one row, but all of the rows must be part of the same table. The basic syntax is:

UPDATE table_name SET column_name=new_value [, ...] WHERE expression

The command requires a table name followed by a list of column name/value pairs that should be assigned. Which rows are updated is determined by a conditional expression that is tested against each row of the table. The most common usage pattern uses the expression to check for equality on some unique column, such as a PRIMARY KEY column.

It is not considered an error if the WHERE expression evaluates to false for every row in the table, resulting in no actual updates.

Here is a more specific example:

-- Update the price and stock of part_id 454:
UPDATE parts SET price = 4.25, stock = 75 WHERE part_id = 454;

This example assumes that the table parts has at least three columns: price, stock, and part_id. The database will find each row with a part_id of 454. In this case, it can be assumed that part_id is a PRIMARY KEY column, so only one row will be updated. The price and stock columns of that row are then assigned new values.

The full syntax for UPDATE can be found at UPDATE in Appendix C.

As you might guess, the DELETE command is used to delete or remove one or more rows from a single table. The rows are completely deleted from the table:

DELETE FROM table_name WHERE expression;

The command requires only a table name and a conditional expression to pick out rows. The WHERE expression is used to select specific rows to delete, just as it is used in the UPDATE command.

As with UPDATE, it is not considered an error if the WHERE expression evaluates to false for every row in the table, resulting in no actual deletions.

Some specific examples:

-- Delete the row with rowid 385:
DELETE FROM parts WHERE part_id = 385;

-- Delete all rows with a rowid greater than or equal to 43
-- and less than or equal to 246:
DELETE FROM parts WHERE part_id >= 43 AND part_id <= 246;

These examples assume we have a table named parts that contains at least one unique column named part_id.

As noted, if no WHERE clause is given, the DELETE command will attempt to delete every row in a table. SQLite optimizes this specific case, truncating the full table, rather than processing each individual row. Truncating the table is much faster than deleting each individual row, but truncation bypasses the individual row processing. If you wish to process each row as it is deleted, provide a WHERE clause that always evaluates to true:

DELETE FROM parts WHERE 1; -- delete all rows, force per-row processing

The existence of the WHERE clause will prevent the truncation, allowing each row to be processed in turn.