SQLite total_changes() Function

The SQLite total_changes() function returns the total number of rows affected by the INSERT, UPDATE, and DELETE statements since the current connection was established.

Syntax

Here is the syntax of the SQLite total_changes() function:

total_changes()

Parameters

The SQLite total_changes() function does not require any parameters.

Return value

The SQLite total_changes() function return an integer that is the total number of rows affected by the INSERT, UPDATE, and DELETE statements since the current connection was established.

The result of the total_changes() function is the cumulative number of rows affected each time, not the actual number of rows.

Examples

To demonstrate what the SQLite total_changes() function can do, we create a table named test_total_changes with the following statement:

CREATE TABLE test_total_changes (
    id INTEGER PRIMARY KEY,
    note VARCHAR(100)
);

Let us insert 2 rows using the following statement,

INSERT INTO test_total_changes (id, note)
VALUES (1, 'Hello'), ('2', 'World');

Let’s use the SQLite total_changes() function to get the total number of rows affected in the current connection:

SELECT total_changes();

total_changes()
---------------
2

Here, the total number of affected rows is 2. This is because the INSERT statement inserted 2 rows.

Let’s use the UPDATE statement to update the row whose id is 2:

UPDATE test_total_changes
set note = 'Everyone'
WHERE id = 2;

Let’s use the SQLite total_changes() function to get the total number of rows affected in the current connection:

SELECT total_changes();

total_changes()
---------------
3

Here, the total number of affected rows is 3. This is because the INSERT statement affects 2 rows and the UPDATE statement affects 1 row.

Finally, let’s delete all rows in the table test_total_changes using the DELETE statement:

DELETE FROM test_total_changes;

Let’s use the SQLite total_changes() function to get the total number of rows affected in the current connection:

SELECT total_changes();

total_changes()
---------------
5

Here, the total number of affected rows is 5. This is because the INSERT statement affects 2 rows, the UPDATE statement affects 1 row, and the DELETE statement affects 2 rows.