SQLite provides several ways to determine how many rows are affected by SQL statements such as INSERT, UPDATE or DELETE.

One way of achieving this is with the changes() function. This function returns the number of rows modified by the most recent SQL statement executed in the current session.

Another way is with the .changes dot command.

In this article, we’ll look at an example that uses these options to get the number of rows changed by various SQL statements.

The changes() Function

The changes() function in SQLite allows you to find out how many rows were affected by the most recent INSERT, UPDATE, or DELETE operation.

Unlike total_changes(), which gives a cumulative count of all changes made in the session, changes() focuses on the immediate previous statement (of the three types mentioned). This makes it particularly useful when you want to confirm the impact of a specific operation.

Key points:

• Most Recent Statement Only: changes() reflects only the last modifying SQL statement.
• Session-Specific: changes() only applies to the current connection.
• Read-Only Queries: If the most recent SQL statement didn’t modify any data (such as a SELECT query), changes() returns the count from the last INSERT, UPDATE, or DELETE statement.

Syntax of changes()

The syntax for the changes() function is simple and does not require any parameters:

changes()

To use changes(), include it in a SELECT statement after executing a data-modifying command.

Example: Using changes() in SQLite

Let’s go through an example to illustrate how the changes() function works. Suppose we have a table named employees where we store information about employees:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    position TEXT,
    salary REAL
);

Step 1: Insert Data into the Table

First, we’ll insert a row into the employees table:

INSERT INTO employees (name, position, salary) VALUES ('Marge', 'Manager', 75000);
INSERT INTO employees (name, position, salary) VALUES ('Lisa', 'Accountant', 65000);
INSERT INTO employees (name, position, salary) VALUES ('Homer', 'Cleaner', 35000);
INSERT INTO employees (name, position, salary) VALUES ('Bart', 'Cleaner', 65000);

To check how many rows were inserted, we use the changes() function immediately after the INSERT statement:

SELECT changes();

Output:

+-----------+
| changes() |
+-----------+
| 1         |
+-----------+

One row was changed.

At first glance, this may seem wrong, seeing as we actually inserted four rows. However, each of these were in its own INSERT statement. Since changes() only reports on the most previous statement (which in this case is the one where we insert Bart), it returns 1.

Step 2: Update Rows in the Table

Now, let’s update the salary of two employees:

UPDATE employees SET salary = 80000 WHERE name = 'Marge' OR name = 'Lisa';

To check how many rows were updated by this command, we run:

SELECT changes();

Output:

+-----------+
| changes() |
+-----------+
| 2         |
+-----------+

Since we updated two rows, the output is 2.

If we try to update a non-existent record, changes() will reflect that no rows were affected. For example:

UPDATE employees SET salary = 85000 WHERE name = 'Nelson';
SELECT changes();

Output:

+-----------+
| changes() |
+-----------+
| 0         |
+-----------+

Since “Nelson” doesn’t exist in the table, changes() returns 0.

Step 3: Delete Rows from the Table

Finally, let’s delete the row where name is “Marge”:

DELETE FROM employees WHERE name = 'Marge';

To check how many rows were deleted, we can use:

SELECT changes();

Output:

+-----------+
| changes() |
+-----------+
| 1         |
+-----------+

As expected, one row was changed.

The .changes Dot Command

The sqlite3 program includes a number of “dot commands”, which are typically used to change the output format of queries, or to execute certain prepackaged query statements. One of these dot commands is .changes.

The .changes dot command is used to output the number of rows changed by SQL. It can be set to either on or off. When it’s on, whenever we run a SQL statement, the number of rows that were changed or inserted or deleted by the most recently completed INSERT, UPDATE or DELETE statement will be output (appended to any results that might be returned by the statement).

Example

First, we need to turn .changes on:

.changes on

Now that it’s on, whenever we run a SQL statement, we’ll see the number of rows affected by the last INSERT, UPDATE or DELETE statement, as well as the total number of changes for the current session:

UPDATE employees SET salary = salary * 1.10 WHERE salary < 70000;

Output:

changes: 2   total_changes: 130

We will see the same number presented even if we run a SQL statement that doesn’t change the data, including when we call the changes() function itself:

SELECT changes();

Output:

+-----------+
| changes() |
+-----------+
| 2         |
+-----------+
changes: 2   total_changes: 130

As mentioned, the .changes dot command also returns the total number of rows that have been changed in the current connection. This number can also be returned with the total_changes() function.