Update anomaly. Mysql sql different rows affected count

Question

SELECT * FROM `attempts` WHERE date = '27-04-2014' LIMIT 0 , 30

This particular query gave 386 results(PHPmyAdmin) but on executing the below query

UPDATE `attempts` SET points = points *2 WHERE date = '27-04-2014'

I got 379 rows affected. . Shouldn't I get same numbers? Any other reasons possible? Or am I wrong somewhere?

Answer 1

The query won't affect the rows where points = 0, because doubling the value of points won't have any effect.

For example, try running this query:

UPDATE `attempts` SET points = points + 0 WHERE date = '27-04-2014'

and it will show 0 rows affected.

Also, the count shown by phpMyAdmin is an estimate, if you're using InnoDB. Use COUNT(*) to get the exact count.

SELECT COUNT(*) FROM `attempts` WHERE date = '27-04-2014'

Answer 2

"Affected rows" counts only rows that were changed. If you have records with 0 points, doubling the number of points has no effect and these records will not be included in the count.