SQL Update大表 [英] SQL Update large table

问题描述

我有一个问题.我需要更新两个大表-t_contact(1.7亿行)和t_participants(1100万行).这些表都具有列CUSTOMER_ID.其中一些ID错误，我需要对其进行更新.错误的ID大约为14万. 我知道如果我要使用UPDATE TABLE会花费很多时间，但是这两个表一定不能长时间不可用.我该怎么办?

I have a question. I need to update two large table - t_contact (170 million rows) and t_participants (11 million rows). This tables both have column CUSTOMER_ID. Some of this IDs wrong and I need to update it. Wrong IDs is about 140 thousand. I understand that if I will use UPDATE TABLE it takes a lot of times, but this two tables mustn't be unavailable for a long time. What should I do?

推荐答案

如果您存储了错误的ID，则应在某些位置使用merge:

If you have the wrong ID's stored some where you should use merge:

MERGE INTO t_contact D
USING (select * from t_wrong_ids) S
ON (D.CUSTOMER_ID = S.NEW_ID)
WHEN MATCHED THEN UPDATE SET D.CUSTOMER_ID = S.OLD_ID

比正常更新快很多.

第二张表是相同的:

MERGE INTO t_participants D
USING (select * from t_wrong_ids) S
ON (D.CUSTOMER_ID = S.NEW_ID)
WHEN MATCHED THEN UPDATE SET D.CUSTOMER_ID = S.OLD_ID

这篇关于SQL Update大表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！