将列从一个表复制到另一个表，但它挂起 SQLite 的 DB Browser

本文介绍了将列从一个表复制到另一个表，但它挂起 SQLite 的 DB Browser的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想将一个表的列复制到另一个表的列，条件是两个员工 ID 应该相等，超过 30 万条记录.

I want to copy one table's column to another table's column with where condition that both employee id should be equal, more than 300k records.

下面是我的查询

UPDATE emp SET emp.address = ( SELECT emp_address FROM address WHERE emp.emp_id=address.emp_id LIMIT 1 );

我在过去 1 小时内执行了此查询，但仍在处理中，我已在DB Browser for SQLite"上执行了此查询.

I have executed this query since last 1 hour but still process is going on, I have executed this query on "DB Browser for SQLite".

我有没有搞错?还是sqlite浏览器有问题?

Am I making any mistake? or there is problem with sqlite browser?

推荐答案

对于这个查询:

UPDATE emp SET emp.address = (SELECT a.emp_address FROM address a WHERE e.emp_id = a.emp_id LIMIT 1 );

您需要 address(emp_id, emp_address) 的索引.

emp(emp_id) 上的索引不应该被这个查询使用并且没有用.如果您在 emp(address) 上有索引，您可能希望删除它并在更新后重新创建它.

An index on emp(emp_id) should not be used by this query and is not useful. If you have an index on emp(address), you might want to drop it and recreate it after the update.

说了这么多，你正在更新表中的所有记录.这很贵.

All that said, you are updating all records in the table. This is expensive.

重新创建表格通常会更快.像这样:

It is often faster to recreate the table. Something like this:

create table temp_emp as select . . . , -- all columns except address (select a.emp_address from address a where e.emp_id = a.emp_id limit 1 ) as address from emp; truncate table emp; -- backup the data first! insert into emp ( . . . ) -- list the columns here select . . . from temp_temp;

您可以创建临时表并查看需要多长时间(使用上述索引).truncate 和 insert 应该不会花费很长时间.但是一定要先备份数据！

You can create the temporary table and see how long that takes (with the above mentioned index). The truncate and insert should not take very long. But do backup the data first!