I ran an update query joining across a huge number of tables that updated one field, and it matched 29k some-odd rows. Running the same query with only an addition in the Set clause increased the matched rows to 118k some-odd rows. It seems this should only change if the WHERE clause were edited.

1 Answer 1


I think I just figured it out...

MySQL must count the number of rows that will be updated, not all the ones that will be generated by the join that happens during the update. The difference being that if there are many [B] to one [A], and many [C] to one [B], joining all would create n([C]) rows, but updating [B] would only count n([B]). Adding a set for [C] makes this number n([B]) + n([C]).

Since the fields in the set clause are on different tables, it changes the number updated.

  • +1 for teaching me that MySQL can update multiple tables in a single update statement - that is pretty unusual I think and not standard SQL. It seems reasonable to me that the 'updated' count would be the total updated across all the tables. May 27, 2011 at 3:19
  • I thought it pretty sweet that MySQL was smart enough to count the updated rows DISTINCTly--rather than counting each row every time it showed up in the query.
    – Bryan Agee
    May 27, 2011 at 15:23
  • The "number of rows updated" is different from "number of rows matches"., number of rows updated is actually the rows that were updated meaning that those rows that had their values set to new ones,. if you set a field in a row to the same value as it was before the update, then the "rows updated" counter is not incremented,. Jun 3, 2011 at 12:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.