How to find number of rows affected in update statement using mysql (not using php mysql).


Recommended Answers

All 7 Replies

brief explanation please....Mr Pradeep

Hi bhanu1225,

Thanks! Actually, am trying to update a record say: update tbl_name set name='bhanu1225' where name='pradeep' and id='1';

If the update is success, i want to print a messgae 'successfully updated' in frontend. For this i want to know if a record is updated in database or not. Am using mysql and jsp.



The ROW_COUNT function will return the number of rows in a previous INSERT, UPDATE or DELETE statement.

Hi Alti,
row_count() is working properly in command prompt, but whereas in query browser,it always shows -1 value even after records are updated successfully.

I would be better if u can give sample code on how to get row_count value in front end.



It seems query browser executes queries on separate connections, or something like that, so when you execute a INSERT/UPDATE/DELETE and then a SELECT ROW_COUNT(); query, the data from the latter will no longer be available to the function.

An easy fix is to just create a wrapper procedure.
For example:


DROP PROCEDURE IF EXISTS `test`.`rc_exec`$$
CREATE PROCEDURE `test`.`rc_exec` (IN command VarChar(60000))
	SET @query = command;
	PREPARE stmt FROM @query;
	EXECUTE stmt;

	SELECT ROW_COUNT() AS 'Affected rows';


Which you could then call like:

CALL rc_exec("
	INSERT INTO myTable(First, Second)
	VALUES  ('Testing First', 'Testing Second'),
                ('Testing First again', 'Teststing Second again');");

Which returns a single field for 'Affected rows' with the value 2.

Ok. Thanks Atli ! Is there any other options like mysql_affected_rows() as in php???

The ROW_COUNT function is the mysql_affected_rows equivalent in MySQL.

Keep in mind that the query browser is just a development tool. It doesn't execute queries the same way a PHP or JSP script would. They wouldn't need the procedure to use the ROW_COUNT function.

For example, this would print "3":

$inResult = mysql_query("INSERT INTO tbl VALUES()()()");
$rcResult = mysql_query("SELECT ROW_COUNT()");
$count = mysql_result($rcResult, 0, 0);
echo $count;

And I'm sure the JSP equivalent for that would to.
And in languages like those, you don't actually have to use the ROW_COUNT function. They have functions like the mysql_num_rows function.

The ROW_COUNT function isn't really mean to be used like this.
It's more aimed at procedures and such.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.