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

问题描述

我需要用我根据 CodeID 创建的新名称替换超过 20 000 个名称.

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

例如:我必须用cat"更新所有包含dog"(CodeID 为 1)的行，并用bird"更新所有包含horse"(CodeID 为 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" WHERE CodeID = 1

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

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

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

这些语句有效，但我需要一种更快的方法来执行此操作，因为我有超过 20 000 个名称.

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?

您可以使用连接进行更新(固定语法......有一段时间没有使用这个)

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屋！