Search code examples
sqlsql-serverms-access

Using "IN" in a WHERE clause where the number of items in the set is very large

I have a situation where I need to do an update on a very large set of rows that I can only identify by their ID (since the target records are selected by the user and have nothing in common other than it's the set of records the user wanted to modify). The same property is being updated on all these records so I would I like to make a single UPDATE call.

Is it bad practice or is there a better way to do this update than using "WHERE IN (1,2,3,4,.....10000)" in the UPDATE statement?

Would it make more sense to use individual update statements for each record and stick them into a single transaction? Right now I'm working with SQL Server and Access but,if possible, I'd like to hear more broad best-practice solutions across any kind of relational database.

Solution

  • I would always use 

    WHERE id IN (1,2,3,4,.....10000)

    unless your in clause was stupidly large, which shouldn't really happen from user input.

    edit: For instance, Rails does this a lot behind the scenes

    It would definitely not be better to do separate update statements in a single transaction.