如何在SQLite的两个表之间复制数据？ [英] How to copy data between two tables in SQLite?

问题描述

我有两个不同列的表格，如下所示：

I have two table with different column like this:

table1
(
    _id,
    title,
    name,
    number,
    address
)

table2
(
    _id,
    phone,
    name,
    address
)

我将数据'name'，'address'从table1复制到table2。

How can I copy data 'name', 'address' from table1 to table2.

我的问题有两种情况：

• 第一：表1，表2中的同一个数据库文件


• 二：表1中data1.db文件，表2中data2.db文件

推荐答案

在SQL复制的工作原理是这样：

Copying in SQL works like so:

insert into table2 (name, address)
select name, address
from table1

如果列 id _ 的值相同，则需要插入和更新

If the values of the column id_ are the same, you need to insert and update

insert into table2 (name, address)
select name, address
from table1 t1
where not exists (select * from table2 t2 where t1._id = t2._id)
;
update table2 t2 name = (select name from table1 t2 where t1._id = t2._id)
;
update table2 t2 address = (select address from table1 t2 where t1._id = t2._id)

如果您需要在数据库之间复制列，则首先将它们导出到一个文件中（使用任何您喜欢的格式，例如CSV），然后手动将该文件合并到第二个数据库，因为您无法编写SQL说使用这些sqlite结构。

If you need to copy the columns between databases, you first export them into a file (use any format you like, for example CSV) and then merge that file into the second database manually since you can't write an SQL which says "use these sqlite structures".

这篇关于如何在SQLite的两个表之间复制数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！