Learn SQLite UPDATE Statement with Examples

Summary: in this tutorial, you will learn how to use SQLite UPDATE statement to update data of existing rows in the table.

Introduction to SQLite UPDATE statement

To update existing data in a table, you use SQLite UPDATE statement. The following illustrates the syntax of the UPDATE statement:

UPDATE table
SET column_1 = new_value_1,
    column_2 = new_value_2
WHERE
    search_condition 
ORDER column_or_expression
LIMIT row_count OFFSET offset;Code language: SQL (Structured Query Language) (sql)

In this syntax:

First, specify the table where you want to update after the UPDATE clause.
Second, set new value for each column of the table in the SET clause.
Third, specify rows to update using a condition in the WHERE clause. The WHERE clause is optional. If you skip it, the UPDATE statement will update data in all rows of the table.
Finally, use the ORDER BY and LIMIT clauses in the UPDATE statement to specify the number of rows to update.

Notice that if use a negative value in the LIMIT clause, SQLite assumes that there are no limit and updates all rows that meet the condition in the preceding WHERE clause.

The ORDER BY clause should always goes with the LIMIT clause to specify exactly which rows to be updated. Otherwise, you will never know which row will be actually updated; because without the ORDER BY clause, the order of rows in the table is unspecified.

SQLite UPDATE statement examples

We will use the employees table in the sample database to demonstrate the UPDATE statement.