有没有一种快速的方法来更新SQL中的许多记录? [英] Is there a fast way to update many records in SQL?

问题描述

在给定CodeID的情况下，我需要用我创建的新名称替换超过20000个名称.

I need to replace more than 20 000 names with new names i created given the CodeID.

例如:我必须用"cat"更新所有包含"dog"(代码ID为1)的行，并用"bird"更新所有包含"horse"(代码ID为2)的行.等

For example: I must update all rows that contain "dog" (which has a CodeID of 1) with "cat", and update all rows that contain "horse" (which has a CodeID of 2) with "bird", etc.

第一个SQL语句:UPDATE animalTable SET cDescription ="cat"其中CodeID = 1

1st SQL statement: UPDATE animalTable SET cDescription = "cat" WHERE CodeID = 1

第二条SQL语句:UPDATE animalTable SET cDescription ="bird"在哪里CodeID = 2

2nd SQL statement: UPDATE animalTable SET cDescription = "bird" WHERE CodeID = 2

这些语句有效，但是我需要一种更快的方法，因为我有2万多个名称.

These statements work, but i need a faster way to do this because i have over 20 000 names.

谢谢.

推荐答案

那是最快的方法.

还是要在一个命令中更新所有记录?

Or do you want update all records in a single command?

您可以使用联接进行更新(固定语法... Havent偶尔使用了此连接)

you can do a update with a join (Fixed Syntax... Havent used this one in a while)

UPDATE animalTable 
INNER JOIN CodeTable ON animalTable.CodeID = CodeTable.ID 
SET animalTable.cDescription = CodeTable.Description_1;

另一种选择是将更新分成较小的批处理，这将减少表被锁定的时间...但是更新的总时间将花费更长的时间(这只是对预期性能的改进).每批中仅更新某些ID范围.

Another option is to split the updates into smaller batches, this will reduce the time the table is locked... But the total time of the updates will take longer (Its just an improvement of precieved Performance) You can do that by updating only certain ID ranges in each batch.

此外，您可以在单独的表中存储该数据.由于数据未规范化.将其移开，使其更加规范化.

Also you could have that data in a separate table. Since the data is not normalized. Move it away so its more normalized.

这篇关于有没有一种快速的方法来更新SQL中的许多记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！