4

Is there a limit on the number of rows that I can update in a single query using an IN clause? For example:

mysql_query("UPDATE `table` SET `row`=1 WHERE `id` IN(1,2,3,4,5,6,7,8....5000)");

Could I update all 5000 rows at once? Or does MySQL have a limit on the number of items in an IN clause?

Please note: I want to know if there is a limit of updates in one single query where MySQL would say something like "maximum number of IN clause entries exceeded". That is, I am inquiring about a possible limit other than the "maximum packet size" setting in MySQL.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Florin Andrei
  • 287
  • 2
  • 11
  • From my personnal experience, when you have doubts on how many elements will be in the list IN (either 100 or 1 million), it is better to split the list in chunks. – glefait Jun 06 '15 at 10:10
  • if the numbers are consistent use WHERE id <5000 or use a table to store those numbers and JOIN on that – Mihai Jun 06 '15 at 13:01
  • I've seen 70K items; it was slow. – Rick James Jun 09 '15 at 21:26

1 Answers1

6

According to the MySQL documentation for the IN function, there is no hard limit on the number of values that can be specified in such a list. The documentation specifically says:

The number of values in the IN list is only limited by the max_allowed_packet value.

However, there is another question here that discusses whether or not there might be a practical limit beyond which a temporary table containing the values might yield better performance when used with a JOIN or an IN(subquery) in the WHERE clause.

Community
  • 1
  • 1
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418