1

When updating a row by UPDATE, is it always guaranteed the the row is 'affected' then, and only then, if some of the fields set in the UPDATE statement would hold NOT field='value' ?

Would that also mean that for any two directly sequential UPDATE's with constant field values the second one would always 'affect' 0 rows?

dronus
  • 10,774
  • 8
  • 54
  • 80

2 Answers2

0

yes, because result show only no of rows that is altered in sql query, so second query does not alter any row as first query done already

Nirmal- thInk beYond
  • 11,847
  • 8
  • 35
  • 46
0

Correct on both counts (though the == operator does not exist in MySQL -- I'm assuming you just used it for clarity).

The rows affected will count only rows that were changed. So if none of the fields are changed, then nothing is affected. Note that this comparison for changed is case sensitive, unlike those actually used in queries.

Edit: http://dev.mysql.com/doc/refman/5.1/en/mysql-affected-rows.html

For UPDATE statements, the affected-rows value by default is the number of rows actually changed. If you specify the CLIENT_FOUND_ROWS flag to mysql_real_connect() when connecting to mysqld, the affected-rows value is the number of rows “found”; that is, matched by the WHERE clause.

I'm looking for a link as to how MySQL decides what consitutes 'actually changed' or not. Based on the wording, it's obvious to assume that they mean it will count as actually changed if an only if the before and after values are not equal on a binary level.

Without diving into source, I probably cannot prove that though.

Corbin
  • 33,060
  • 6
  • 68
  • 78
  • I also thought of several type conversion related problems.. For example I could imagine that a date given with day resolution would maybe equal a `DATETIME` with the same day, however the `UPDATE` would affect the row by setting the time to 0:00:00. – dronus Mar 22 '12 at 10:42
  • With all those possible conversions (strings<->numbers<->times), I now have the bad feeling the answer to my first statement would be 'no'. – dronus Mar 22 '12 at 10:52
  • That would lead to another question: How to know if some given data used in an `UPDATE` statement would affect rows without actually executing it? – dronus Mar 22 '12 at 10:53