MySQL Bugs: #1255: add sql syntax for AFFECTED

Bug #1255	add sql syntax for AFFECTED_ROWS
Submitted:	11 Sep 2003 14:14	Modified:	7 Oct 2005 21:40
Reporter:	Michael Gile	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	all	OS:	Any (all)
Assigned to:		CPU Architecture:	Any

Description:
difficult to determine number of affected rows or success of query from apps like coldfusion

How to repeat:
create an insert, update, or delete cfquery in coldfusion, try to determine number of affected rows

Suggested fix:
It would be nice to be able to select the affected_rows from SQL, similar to the @@ROWCOUNT in MS-SQL.  This is useful for things like coldfusion where you can do the following

<cfquery name="x">
insert into test values();
select affected_rows();
</cfquery>

Which you can use to test if the insert succeded or failed.

Implemented since MySQL 5.0.3:

ROW_COUNT()

ROW_COUNT() returns the number of rows updated, inserted, or deleted by the preceding statement. This is the same as the row count that the mysql client displays and the value from the mysql_affected_rows() C API function.

mysql> INSERT INTO t VALUES(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

mysql> DELETE FROM t WHERE i IN(1,2);
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

ROW_COUNT() was added in MySQL 5.0.1.