Get the Number of Rows Affected by Previous SQL Statement

Posted on by Ian

Some RDBMSs provide an easy way for us to find out how many rows were affected by the last SQL statement. This can be handy when running INSERT, UPDATE, or DELETE statements.

The method used depends on the DBMS we’re using. Below, I look at how some of the major DBMSs implement this functionality.

MySQL

MySQL provides us with a ROW_COUNT() function that returns the number of rows updated, inserted, or deleted by the preceding statement.

To demonstrate how ROW_COUNT() works, let’s insert some data into a table:

INSERT INTO Idiots ( IdiotName ) VALUES 
    ( 'Dumb' ),
    ( 'Dumber' ),
    ( 'Dumbest' );

Result:

Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

Now let’s run ROW_COUNT():

SELECT ROW_COUNT();

Result:

+-------------+
| ROW_COUNT() |
+-------------+
|           3 |
+-------------+

In this example we inserted three rows into the table, and ROW_COUNT() therefore reported that three rows were affected.

The result from this function will depend on the type of statement run. It’s mainly for DML statements. For example, if we run a DDL statement such as CREATE TABLE or DROP TABLE , the ROW_COUNT() function returns 0. If we run a statement that returns a result set (such as a SELECT statement) it returns -1.

See MySQL ROW_COUNT() Explained for more info and examples.

MariaDB

MariaDB has its own ROW_COUNT() function that works similar to MySQL’s function of the same name. Specifically, it returns the number of rows updated, inserted, or deleted by the preceding statement.

Let’s insert three values into a table, but this time we’ll use three different INSERT statements instead of just one like we did with MySQL:

INSERT INTO guest (guest_name) VALUES ('Homer');
INSERT INTO guest (guest_name) VALUES ('Bart');
INSERT INTO guest (guest_name) VALUES ('Marge');

Result:

MariaDB [Zap]> INSERT INTO guest (guest_name) VALUES ('Homer');
Query OK, 1 row affected (0.037 sec)

MariaDB [Zap]> INSERT INTO guest (guest_name) VALUES ('Bart');
Query OK, 1 row affected (0.001 sec)

MariaDB [Zap]> INSERT INTO guest (guest_name) VALUES ('Marge');
Query OK, 1 row affected (0.002 sec)

Each statement resulted in one row being affected.

Now let’s run ROW_COUNT():

SELECT ROW_COUNT();

Result:

+-------------+
| ROW_COUNT() |
+-------------+
|           1 |
+-------------+

This returns 1 because that’s how many rows were affected in the last statement. Even though we affected three rows, it took three statements to do that (each statement inserted just one row, and ROW_COUNT() only reports on the last statement).

So just to be sure, if we’d inserted all three in the same statement, ROW_COUNT() would have returned 3, just like in our MySQL example earlier.

See MariaDB ROW_COUNT() Explained for more information and examples.

SQL Server

SQL Server has the @@ROWCOUNT system function that returns the number of rows affected by the last T-SQL statement.

This function differs from the MySQL and MariaDB functions mentioned on this page in that it works on SELECT statements (in addition to DDL statements like INSERT, UPDATE, and DELETE).

Example:

SELECT * FROM Dogs;
SELECT @@ROWCOUNT;

Result:

+---------+-----------+-----------+
| DogId   | DogName   | GoodDog   |
|---------+-----------+-----------|
| 1       | Fetch     | 0         |
| 2       | Fluffy    | 0         |
| 3       | Wag       | 0         |
+---------+-----------+-----------+
(3 rows affected)
+--------------------+
| (No column name)   |
|--------------------|
| 3                  |
+--------------------+
(1 row affected)

In this case, my SELECT statement returned 3 rows, and so @@ROWCOUNT returned 3.

See @@ROWCOUNT – Get the Number of Rows Affected by the Last Statement in SQL Server for more info and examples.

Also if you expect more than 2 billion rows were affected, use the ROWCOUNT_BIG() function instead. This function behaves just like @@ROWCOUNT, except that it returns a BIGINT value (as opposed to an INT from @@ROWCOUNT).

SQLite

SQLite provides us with its CHANGES() function that does pretty much the same thing as MySQL and MariaDB’s ROW_COUNT() function. It returns the number of database rows that were changed, inserted or deleted by the most recently completed INSERT, DELETE, or UPDATE statement, exclusive of statements in lower-level triggers.

Here’s an example of using SQLite’s CHANGES() in a DELETE statement:

DELETE FROM Dogs 
WHERE DogId IN ( 4, 6 );

Now let’s run the changes() function to see how many rows were changed:

SELECT CHANGES();

Result:

CHANGES()
---------
2

As expected, it tells us that two rows were changed.

Only changes made directly by the INSERT, UPDATE, or DELETE statement are considered. Auxiliary changes caused by triggers, foreign key actions or REPLACE constraint resolution are not counted.

See SQLite CHANGES() Function for more info and examples.

PostgreSQL

In PostgreSQL, we can use the RETURNING clause to return data that was modified during an UPDATE, INSERT, or DELETE operation. This allows us to see the actual rows that were affected, or one or more columns, as we choose.

Example:

CREATE TABLE Idiots (
    IdiotId SERIAL PRIMARY KEY,
    IdiotName VARCHAR
);

INSERT INTO Idiots ( IdiotName ) 
VALUES ( 'Dumb' ), ( 'Dumber' ), ( 'Dumbest' )
RETURNING IdiotId;

Result:

 idiotid 
---------
       1
       2
       3
(3 rows)

We can see the values of the IdiotId column that were inserted. As mentioned, the same applies with UPDATE and DELETE statements.

See How to Return Data from Modified Rows in PostgreSQL for more information and examples.

Also, see the PostgreSQL documentation for obtaining the query result status for some other alternatives.

And if you’re connecting to Postgres via PHP, check out the pg_affected_rows function. That function is specifically for returning the number of affected records affected by INSERT, UPDATE, and DELETE queries.