是否可以使用一个UPDATE SQL语句执行多个更新? [英] Is it possible to perform multiple updates with a single UPDATE SQL statement?
问题描述
假设我有一个表 tbl ，其中有列 id 和 title . 我需要更改标题栏的所有值:
Let's say I have a table tbl with columns id and title. I need to change all values of title column:
- 从"a-1"到"a1"，
- 从"a.1"到"a1"，
- 从"b-1"到"b1"，
- 从"b.1"到"b1".
现在，我正在执行两个UPDATE语句:
Right now, I'm performing two UPDATE statements:
UPDATE tbl SET title='a1' WHERE title IN ('a-1', 'a.1')
UPDATE tbl SET title='b1' WHERE title IN ('b-1', 'b.1')
这根本不是问题，如果表很小，并且单个语句在不到一秒钟的时间内完成，并且您只需要执行几个语句即可.
This isn't at all a problem, if the table is small, and the single statement completes in less than a second and you only need a few statements to execute.
您可能对它进行了邀请-我有一个庞大的表要处理(一个语句在大约90秒内完成)，并且我有大量要执行的更新.
You probably guested it - I have a huge table to deal with (one statement completes in about 90 seconds), and I have a huge number of updates to perform.
那么，是否可以合并更新，使其仅扫描表一次?也许，在这种情况下有更好的应对方法.
So, is it possible to merge the updates so it would only scan the table once? Or perhaps, there's a better way to deal with in a situation like this.
请注意，我正在使用的真实数据和对我必须执行的数据的更改并不是那么简单-字符串更长，并且它们不遵循任何模式(它是用户数据，因此无法做任何假设-可以是任何东西.
Note, that the real data I'm working with and the changes to the data I have to perform are not really that simple - the strings are longer and they don't follow any pattern (it is user data, so no assumptions can be made - it can be anything).
推荐答案
在更一般的情况下，每个新值可能有数百个映射，您将创建一个新旧值的单独表，然后在UPDATE语句中使用它.在SQL的一种方言中:
In a more general case, where there could be many hundreds of mappings to each of the new values, you would create a separate table of the old and new values, and then use that in the UPDATE statement. In one dialect of SQL:
CREATE TEMP TABLE mapper (old_val CHAR(5) NOT NULL, new_val CHAR(5) NOT NULL);
...multiple inserts into mapper...
INSERT INTO mapper(old_val, new_val) VALUES('a.1', 'a1');
INSERT INTO mapper(old_val, new_val) VALUES('a-1', 'a1');
INSERT INTO mapper(old_val, new_val) VALUES('b.1', 'b1');
INSERT INTO mapper(old_val, new_val) VALUES('b-1', 'b1');
...etcetera...
UPDATE tbl
SET title = (SELECT new_val FROM mapper WHERE old_val = tbl.title)
WHERE title IN (SELECT old_val FROM mapper);
两个select语句都是至关重要的.第一个是相关子查询(如果映射器表具有数千行，则它不一定快速，但比大多数替代方法要快)，它将新值从对应于旧值的映射表中拉出.第二个确保只有在映射表中具有值的那些行才被修改；这非常重要，因为否则，对于那些没有映射条目的行(以及那些在开始之前就可以的记录)，标题将设置为null.
Both select statements are crucial. The first is a correlated sub-query (not necessarily fast, but faster than most of the alternatives if the mapper table has thousands of rows) that pulls the new value out of the mapping table that corresponds to the old value. The second ensures that only those rows which have a value in the mapping table are modified; this is crucial as otherwise, the title will be set to null for those rows without a mapping entry (and those were the records that were OK before you started out).
对于其他一些选择，CASE操作还可以.但是，如果要执行数百，数千或数百万个映射，那么您可能会超出DBMS中SQL语句长度的限制.
For a few alternatives, the CASE operations are OK. But if you have hundreds or thousands or millions of mappings to perform, then you are likely to exceed the limits of the SQL statement length in your DBMS.
这篇关于是否可以使用一个UPDATE SQL语句执行多个更新?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
