SQLite - UPDATE Query

SQLite - UPDATE Query

The SQLite UPDATE query is used to modify existing records in a table. You almost always use a WHERE clause with an UPDATE statement to specify which rows to change. Omitting the WHERE clause will update all rows in the table.

Syntax

The basic syntax of an UPDATE query is as follows. The SET clause specifies which columns to change and their new values.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Modern SQLite (3.35.0+) also supports a RETURNING clause, which is extremely useful for getting the updated data back in a single step.

UPDATE table_name
SET column1 = value1
WHERE condition
RETURNING column1, column2, ...;

Example Scenarios

Consider an EMPLOYEES table with the following data:

ID          NAME        TITLE               SALARY
----------  ----------  ------------------  ----------
1           Alice       Software Engineer   90000.0
2           Bob         Project Manager     110000.0
3           Charlie     Software Engineer   95000.0

1. Updating a Single Record

Let’s update the TITLE for the employee with ID 1.

UPDATE EMPLOYEES
SET TITLE = 'Senior Software Engineer'
WHERE ID = 1;

2. Updating Based on Current Values

Let’s give all Software Engineers a 5% salary increase.

UPDATE EMPLOYEES
SET SALARY = SALARY * 1.05
WHERE TITLE LIKE '%Software Engineer%';

After this query, Alice’s salary would be 94500.0 and Charlie’s would be 99750.0.

3. Using the RETURNING Clause

Let’s promote Bob and see his new salary immediately, without a separate SELECT query.

sqlite> UPDATE EMPLOYEES
   ...> SET TITLE = 'Director of Engineering', SALARY = 140000.0
   ...> WHERE ID = 2
   ...> RETURNING NAME, TITLE, SALARY;

NAME        TITLE                    SALARY
----------  -----------------------  ----------
Bob         Director of Engineering  140000.0

Best Practices and Safety

• The Golden Rule: Double-check your WHERE clause. An UPDATE without a WHERE clause will modify every single row in the table. This is rarely what you want.
• Preview Your Changes: Before running a destructive UPDATE, run a SELECT statement with the same WHERE clause to see which rows will be affected. SELECT * FROM table_name WHERE condition;
• Use Transactions: For critical or multi-step updates, wrap them in a transaction. This allows you to ROLLBACK the changes if you make a mistake or if a later step fails.
• Be Specific: Use primary keys in your WHERE clause (WHERE ID = ?) when you intend to update only one specific row. This is safer and usually faster than using other columns.