将列从一个表复制到另一个表，但它挂起 SQLite 的 DB Browser [英] Copy column from one table to another table but it hangs DB Browser for SQLite

问题描述

我想将一个表的列复制到另一个表的列，条件是两个员工 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!

这篇关于将列从一个表复制到另一个表，但它挂起 SQLite 的 DB Browser的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！