MySQL Multiple updates using PK or one single update using IN (Maximum IN Size?)

Question

IdList = 1,2,3,4,5,6,7,8,9,10

better:

Foreach(id in IdList)
    Update MyTable set isDeleted = 1 where id = ?id

or

Update MyTable set isDeleted = 1 where id IN (?IdList)

Forgive my pseudocode, but I think it should be clear. I know there are limits to the size of the list in an IN clause. It used to be 255 in Oracle.

I'm thinking multiple sqls will be slower, but maybe parsing the IdList will cause more overhead for the DB Server. Would it be better if the list was sorted?

Answer 1

The fewer queries to the database, the better. That said, this:

Update MyTable set isDeleted = 1 where id IN (?IdList)

...requires dynamic SQL because SQL doesn't support a variable containing a comma separated list of values in an IN clause.

If you're constructing the query as a string before submitting it, concatenate the variable into that string before submission. Otherwise you'll have to use MySQL's dynamic SQL syntax, called PreparedStatements. See this link for Oracle dynamic SQL examples & info.

Answer 2

When the choice is between a cursor-based solution (the first) and a set-based solution (the second), set-based wins out most of the time - and in this case I would definitely plump for the second of the two options - it'll be considerably more efficient and easier to read / understand.