SQLite | SQLite Functions | Count Rows Changed by the Most Recent SQL Statement (changes)

The changes() function returns the number of rows inserted, updated, or deleted by the most recently executed SQL statement. This article explains how to use it.

To count all rows changed since the database connection was opened, use the total_changes() function.

Using the changes Function

Use changes() to obtain the number of rows processed by the latest INSERT, UPDATE, or DELETE. The syntax is as follows.

changes()

It returns the number of table rows actually processed by an INSERT, UPDATE, or DELETE statement.

–

Try it with an example. 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 the following rows.

insert into user values (1, 'devkuma', 'Seoul');
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 (1, 'devkuma', 'Seoul');
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> 

After the inserts, call changes() to retrieve the number of rows processed by the latest statement.

select changes();

Although several INSERT statements ran, the latest one inserted only one row, so changes() returns 1.

sqlite> create table user (id integer, name text, address text);
sqlite> 
sqlite> insert into user values (1, 'devkuma', 'Seoul');
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> 
sqlite> select changes();
1
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 modified two rows, so changes() returns 2.

select changes();

sqlite> update user set address = 'suwon' where address = 'Suwon';
sqlite> 
sqlite> select changes();
2
sqlite> 

–

Next, delete some rows.

delete from user where address = 'Paju';

sqlite> delete from user where address = 'Paju';
sqlite> 

The DELETE removed two rows, so changes() returns 2.

select changes ();

sqlite> delete from user where address = 'Paju';
sqlite> 
sqlite> select changes();
2

In this way, changes() reports how many rows the latest INSERT, UPDATE, or DELETE processed.