使用"IN"在WHERE子句中，该集合中的项数非常大 [英] Using "IN" in a WHERE clause where the number of items in the set is very large

问题描述

我遇到的情况是，我需要对非常大的一组行进行更新，这些行只能根据其ID进行标识(因为目标记录是由用户选择的，除了这组记录外，没有其他共同点)记录要修改的用户).正在所有这些记录上更新相同的属性，因此我想进行一个UPDATE调用.

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.

这是不好的做法还是有比使用UPDATE语句中的"WHERE IN(1,2,3,4，..... 10000)"更好的方法来进行此更新?

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?

为每条记录使用单独的更新语句并将它们粘贴到单个事务中会更有意义吗?现在，我正在使用SQL Server和Access，但是，如果可能的话，我想听听有关任何类型的关系数据库的更广泛的最佳实践解决方案.

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.

推荐答案

我将始终使用

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

除非您的in子句愚蠢大，否则这实际上不应从用户输入中发生.

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

例如，Rails在幕后做了很多事情

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.

这篇关于使用"IN"在WHERE子句中，该集合中的项数非常大的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！