使用 CSV 中的多个更新语句与使用中间表
我想更新大约 10,000 条记录。更新信息位于多个 CSV 文件中。更新表的一种方法是
- 创建中间表并从该表更新我的主表（更多工作）
- 使用 CSV 到 SQL 实用程序创建多个 UPDATE 语句（这很诱人）
我的问题是，创建数千个更新语句是否安全像这样？它会出现问题并可能损坏我的数据吗？完成这个动作确实需要很多时间。 这个就是这样一个在线工具
UPDATE MyTable SET change_field = 'ABC' WHERE other_field = '1';
UPDATE MyTable SET change_field = 'ABC' WHERE other_field = '2';
UPDATE MyTable SET change_field = 'DEF' WHERE other_field = '3';
I want to update about 10,000 records. The update information is in multiple CSV files. One way to update my table is
- Create intermediate table and update my primary table from this table (more work)
- Use CSV to SQL utility which creates multiple UPDATE statements (this is tempting)
My question is, is it safe to create thousand of Update Statements like this? Can it be problematic and possibly corrupt my data? It does take a lot of time to complete the action. One such online tool is this
UPDATE MyTable SET change_field = 'ABC' WHERE other_field = '1';
UPDATE MyTable SET change_field = 'ABC' WHERE other_field = '2';
UPDATE MyTable SET change_field = 'DEF' WHERE other_field = '3';
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
运行大量这样的语句应该不会有任何问题，除非您使用的数据库有某种专门记录的限制。不过，一般来说，对于大多数数据库来说，运行 10,000 个更新语句不会被认为是太大的负载。您可能需要考虑在事务中运行语句，以便在出现问题时可以回滚更改。
最好确保
other_field有索引。否则，如果表非常大，则更新语句可能会很慢（如果该字段上没有索引，则每次更新可能需要全表扫描）。There shouldn't be any problem running a large number of statements like that unless the database you are using has some kind of limitation specifically documented. In general, though, running 10,000 update statements would not be considered much of a load for most databases. You might want to consider running the statements in a transaction so that if there are problems you can roll back the changes.
It would be good to make sure that
other_fieldhas an index on it. Otherwise, the update statements could be slow if the table is very large (each update would likely require a full table scan if there is no index on that field).