[SOLVED] Number of rows affected by UPDATE - in a stored procedure

[miseleigh]

The MySQL manual says that the update statement returns the number of affected rows.  We're trying to figure out how to use that feature, and it seems that few people use it or even think about using it.  The way we've been doing it in the past has been performing the update and then doing a select to get the count.  However, we're trying to reduce the size of our stored procedure (we need it faster!) and combining those into one statement would help.

 

Any tips on this one?  It's been hard to find anything online.  Basically, in the stored procedure, we want something like

SET v_count = 
     UPDATE Users 
          SET active=1 
          WHERE username=v_username);

instead of

UPDATE Users 
     SET active=1 
     WHERE username=v_username;
SELECT COUNT(username) 
     INTO v_count 
     FROM Users 
     WHERE active=1 AND username=v_username;

 

 

[miseleigh]

Nevermind, we found a better way to do it anyway, but for future reference for anyone else - it's called ROW_COUNT().

 

Just call SELECT ROW_COUNT() after an insert, update, or select, and you'll have the number of affected rows.