Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
    
+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. –  Jack Douglas May 27 '11 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 '11 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,. –  ovais.tariq Jun 3 '11 at 12:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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