SQL sqlite 将数据从一张表复制到另一张表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4291165/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sqlite copy data from one table to another
提问by Ian Vink
SQLITE
SQLITE
I have 2 tables "Source" and "Destination" that have the same fields. ID and COUNTRY, though they both have other fields too that are not in common.
我有 2 个具有相同字段的表“源”和“目标”。ID 和 COUNTRY，尽管它们都有其他不共同的字段。
I need to copy the Source.Country value to the Destination.Country where the join is on ID
我需要将 Source.Country 值复制到连接在 ID 上的 Destination.Country
For the life of me I can't make Sqlite do this.
对于我的生活，我不能让 Sqlite 这样做。
In SQL Server etc this is a super simple task.
在 SQL Server 等中，这是一项超级简单的任务。
Ideas?
想法？
回答by joschi
INSERT INTO Destination SELECT * FROM Source;
See SQL As Understood By SQLite: INSERTfor a formal definition.
请参阅SQLite 所理解的 SQL：插入以获取正式定义。
回答by W00di
If you have data already present in both the tables and you want to update a table column values based on some condition then use this
如果两个表中都已存在数据，并且想要根据某些条件更新表列值，请使用此
UPDATE Table1 set Name=(select t2.Name from Table2 t2 where t2.id=Table1.id)
回答by Max
I've been wrestling with this, and I know there are other options, but I've come to the conclusion the safest pattern is:
我一直在努力解决这个问题，我知道还有其他选择，但我得出的结论是最安全的模式是：
create table destination_old as select * from destination;
drop table destination;
create table destination as select
d.*, s.country
from destination_old d left join source s
on d.id=s.id;
It's safe because you have a copy of destinationbefore you altered it. I suspect that update statements with joins weren't included in SQLite because they're powerful but a bit risky.
这是安全的，因为您destination在更改它之前有一个副本。我怀疑 SQLite 中没有包含带有连接的更新语句，因为它们功能强大但有点冒险。
Using the pattern above you end up with two countryfields. You can avoid that by explicitly stating all of the columns you want to retrieve from destination_oldand perhaps using coalesceto retrieve the values from destination_oldif the countryfield in sourceis null. So for example:
使用上面的模式，您最终会得到两个country字段。您可以通过显式声明要从中检索的所有列来避免这种情况，destination_old并且如果字段中的字段为空coalesce，destination_old则可能用于从中检索值。例如：countrysource
create table destination as select
d.field1, d.field2,...,coalesce(s.country,d.country) country
from destination_old d left join source s
on d.id=s.id;
回答by Niels Bom
If you're copying data like that, that probably means your datamodel isn't fully normalized, right? Is it possible to make one list of countries and do a JOIN more?
如果您像这样复制数据，那可能意味着您的数据模型没有完全规范化，对吗？是否可以制作一个国家/地区列表并进行更多 JOIN？
Instead of a JOIN you could also use virtual tables so you don't have to change the queries in your system.
除了 JOIN，您还可以使用虚拟表，这样您就不必更改系统中的查询。
