Outdated Version

You are viewing an older version of this section. View current production version.

ROW_COUNT

Returns the number of rows affected by the last INSERT, UPDATE, or DELETE command. It can be used from client applications, stored procedures, and the command line.

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();

Results:

+-------------+
| 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:

memsql> 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`

memsql> ECHO delete_account(1);
+--------+
| RESULT |
+--------+
|      1 |
+--------+