I have millions of records in a table and I need to update particular records which have wrong values. How do I do it?
我在表中有数百万条记录,我需要更新具有错误值的特定记录。我该怎么做?
Example:
Si Item_Id
1 T21547856
2 T45200254
3 T54785000
Need to update like:
需要更新如下:
T21547856 = CS2541
T54785000 = CS5475
This is just an example. I have millions of records and need to update more than half a million.
这只是一个例子。我有数百万条记录,需要更新超过50万条。
1 个解决方案
#1
One approach would be:
一种方法是:
Create an index on item_id, then just do the updates. update table set item_id = 'CS2541' where itme_id = 'T21547856'
在item_id上创建索引,然后只进行更新。更新表set item_id ='CS2541'其中itme_id ='T21547856'
This works only item_ids are unique in your table.
这只适用于item_ids在您的表中是唯一的。
After this, you may drop the index if you don't need it.
在此之后,如果您不需要,可以删除索引。
A second approach would be to create another table, B, with values to be updated:
第二种方法是创建另一个表B,其中包含要更新的值:
si item_id
1 CS2541
3 CS5475
Then do a merge:
然后进行合并:
merge into your_table a
using b
on a.si=b.si
when matched then update set a.item_id=b.item_id;
#1
One approach would be:
一种方法是:
Create an index on item_id, then just do the updates. update table set item_id = 'CS2541' where itme_id = 'T21547856'
在item_id上创建索引,然后只进行更新。更新表set item_id ='CS2541'其中itme_id ='T21547856'
This works only item_ids are unique in your table.
这只适用于item_ids在您的表中是唯一的。
After this, you may drop the index if you don't need it.
在此之后,如果您不需要,可以删除索引。
A second approach would be to create another table, B, with values to be updated:
第二种方法是创建另一个表B,其中包含要更新的值:
si item_id
1 CS2541
3 CS5475
Then do a merge:
然后进行合并:
merge into your_table a
using b
on a.si=b.si
when matched then update set a.item_id=b.item_id;