SQLite: UPDATE Statement

This SQLite post explains how to use the SQLite UPDATE statement with syntax and examples.

Description

The SQLite UPDATE statement is used to update existing records in a table in a SQLite database. There are 2 syntaxes for the UPDATE statement depending on the type of update that you wish to perform.

Syntax

In its simplest form, the syntax for the SQLite UPDATE statement when updating one table is:

UPDATE table
SET column1 = expression1,
    column2 = expression2,
    ...
[WHERE conditions];

However, the full syntax for the SQLite UPDATE statement when updating one table is:

UPDATE table
SET column1 = expression1,
    column2 = expression2,
    ...
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]]
[LIMIT number_rows OFFSET offset_value];

OR

The syntax for the MySQL UPDATE statement when updating one table with data from another table is:

UPDATE table1
SET column1 = (SELECT expression1
               FROM table2
               WHERE conditions)
[WHERE conditions];

Parameters or Arguments

table

The name of the table that you wish to update.

column1, column2

The columns that you wish to update.

expression1, expression2

The new values to assign to the column1, column2. So column1 would be assigned the value of expression1, column2 would be assigned the value of expression2, and so on.

WHERE conditions

Optional. The conditions that must be met for the update to execute.

ORDER BY expression

Optional. It may be used in combination with LIMIT to sort the records appropriately when limiting the number of records to be updated. ASC sorts in ascending order and DESC sorts in descending order.

LIMIT number_rows OFFSET offset_value

Optional. If LIMIT is provided, it controls the maximum number of records to update in the table. At most, the number of records specified by number_rows will be update in the table. The first row selected by LIMIT will be determined by offset_value.

Example - Update single column

Let's look at a very simple SQLite UPDATE query example.

UPDATE employees
SET last_name = 'Johnson'
WHERE employee_id = 1;

This SQLite UPDATE example would update the last_name to 'Johnson' in the employees table where the employee_id is 1.

Example - Update multiple columns

Let's look at a SQLite UPDATE example where you might want to update more than one column with a single UPDATE statement.

UPDATE employees
SET last_name = 'Johnson',
    favorite_website = 'AODBA.com'
WHERE employee_id = 1;

When you wish to update multiple columns, you can do this by separating the column/value pairs with commas.

This SQLite UPDATE statement example would update the last_name to 'Johnson' and the favorite_website to 'AODBA.com' where the employee_id is equal to 1.

Example - Update table with data from another table

Let's look at an UPDATE example that shows how to update a table with data from another table in SQLite.

UPDATE employees SET city = (SELECT city             FROM offices             WHERE office_id = 1) WHERE employee_id > 10;

This UPDATE example would update only the employees table for all records where the employee_id is greater than 10. The city from the offices table where office_id is equal to 1 would be copied to the city field in the employees table.