SQLite | SQLite Functions | Count All Rows Changed Since Connecting (total_changes)

The total_changes() function returns the total number of rows inserted, updated, or deleted since the database connection was opened. This article explains how to use it.

To count rows changed by only the most recent SQL statement, use changes().

Using the total_changes Function

Use total_changes() to obtain the total number of rows processed by INSERT, UPDATE, and DELETE statements during the current connection. The syntax is as follows.

total_changes()

It returns the cumulative number of table rows actually processed after connecting.

–

Connect to a database to try an example.

$ sqlite3 total_changes.sqlite3
SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
sqlite> 

Create the following table.

create table user (id integer, name text, address text);

sqlite> create table user (id integer, name text, address text);
sqlite> 

Insert one row.

insert into user values (1, 'devkuma', 'Seoul');

sqlite> insert into user values (1, 'devkuma', 'Seoul');
sqlite> 

Call total_changes() to retrieve the number of rows processed so far.

select total_changes ();

One row has been inserted since connecting, so total_changes() returns 1.

sqlite> .mode column
sqlite> .header on
sqlite> 
sqlite> select total_changes();
total_changes()
---------------
1              
sqlite> 

Insert seven more rows.

insert into user values (2, 'kimkc', 'Busan');
insert into user values (3, 'araikuma', 'Paju');
insert into user values (4, 'happykuma', 'Paju');
insert into user values (5, 'mykuma', 'Daejeon');
insert into user values (6, 'yourkuma', 'Seongnam');
insert into user values (7, 'raccoon', 'Suwon');
insert into user values (8, 'noguri', 'Suwon');

sqlite> insert into user values (2, 'kimkc', 'Busan');
sqlite> insert into user values (3, 'araikuma', 'Paju');
sqlite> insert into user values (4, 'happykuma', 'Paju');
sqlite> insert into user values (5, 'mykuma', 'Daejeon');
sqlite> insert into user values (6, 'yourkuma', 'Seongnam');
sqlite> insert into user values (7, 'raccoon', 'Suwon');
sqlite> insert into user values (8, 'noguri', 'Suwon');
sqlite> 

Call total_changes() again.

select total_changes();

The connection has now processed eight inserted rows in total, so the function returns 8.

Using total_changes (6)

sqlite> select total_changes();
total_changes()
---------------
8              
sqlite> 

–

Next, update some rows.

update user set address = 'suwon' where address = 'Suwon';

sqlite> update user set address = 'suwon' where address = 'Suwon';
sqlite> 

The update changes two rows, increasing the cumulative total from 8 to 10, so total_changes() returns 10.

select total_changes();

sqlite> select total_changes();
total_changes()
---------------
10             
sqlite> 

Thus, total_changes() reports the cumulative number of rows processed by INSERT, UPDATE, and DELETE during a connection.