ROW_COUNT

Returns the number of rows inserted, updated, or deleted by the preceding statement.

Syntax

ROW_COUNT()

Arguments

  • None.

Return Type

int

Examples

The following shows how to use ROW_COUNT() in a query after running an INSERT:

DROP TABLE IF EXISTS account;
CREATE TABLE account(id int, amount decimal(10,2));
INSERT account VALUES(1, 10.00), (2, 30.00);
SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
|           2 |
+-------------+

This example shows how an application can raise an error if a ROW_COUNT() result is different than expected after a DELETE:

DELIMITER //
CREATE OR REPLACE PROCEDURE delete_account(_id int) RETURNS int AS
DECLARE _row_count int = -1;
BEGIN
DELETE FROM account WHERE account.id = _id;
_row_count = row_count();
IF _row_count = 0 THEN
RAISE user_exception(CONCAT("No rows deleted for account ", _id));
END IF;
RETURN _row_count;
END //
DELIMITER ;

Example use:

ECHO delete_account(3);
ERROR 2242 (HY000): Unhandled exception
Type: ER_USER_RAISE
Message: No rows deleted for account 3
Callstack:
#0 Line 7 in `db1`.`delete_account`
ECHO delete_account(1);
+--------+
| RESULT |
+--------+
| 1 |
+--------+

Last modified: February 24, 2023

Was this article helpful?