1

I was wandering what's the limit for a query to update a good number of fields in a single table using an "IN" clause.

I'm talking about a range between a 5,000 to 2,000,000 rows in each call.

Is this something I should be concern about?

The sample query can be something like:

UPDATE table_name SET
    field1 = '',
    field2 = '',
    field3 = '',
    field4 = '',
    field5 = '',
    field6 = '',
    field7 = ''
WHERE id IN (
    1,
    2,
    3,
    ...,
    999998,
    999999,
    1000000
)
Ali
  • 2,993
  • 3
  • 19
  • 42
  • 1
    There's no right answer for this question; it depends on too many things. Just test it out and see where it breaks. – miken32 Jan 20 '16 at 20:30
  • Thanks @mike32, any chance you could mentioned a few of those many things it depends on? this will be a great help to me to get better understand about potential disadvantages of such approach. – Ali Jan 20 '16 at 20:34
  • 4
    Take a look at this: http://stackoverflow.com/a/8808935/4949388 – Jonathan Parent Lévesque Jan 20 '16 at 20:39
  • Thanks @JonathanParentLévesque, it was really helpful :) – Ali Jan 20 '16 at 22:29

1 Answers1

0

There is no limitation on number of records to upddate by any condition clause, including IN on the mysqlside, however there are platform and performance related stuff which you might want to concider. have a look at How to update 10 million+ rows in MySQL single table as Fast as possible?

Community
  • 1
  • 1