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.

我有两个具有相同字段的表“Source”和“Destination”。ID和国家，虽然它们都有其他领域不一样。

I need to copy the Source.Country value to the Destination.Country where the join is on ID

我需要复制源文件。国家对目的地的价值。连接在ID上的国家

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?

想法吗?

4 个解决方案

#1

105

INSERT INTO Destination SELECT * FROM Source;

See SQL As Understood By SQLite: INSERT for a formal definition.

参见SQLite: INSERT获得正式定义所理解的SQL。

#2

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)

#3

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?

如果你像这样复制数据，那可能意味着你的数据模型没有完全标准化，对吧?是否有可能列出一个国家的名单，并加入更多的国家?

Instead of a JOIN you could also use virtual tables so you don't have to change the queries in your system.

您也可以使用虚拟表来代替连接，这样就不必更改系统中的查询。

#4

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 destination before you altered it. I suspect that update statements with joins weren't included in SQLite because they're powerful but a bit risky.

它是安全的，因为你在更改目的地之前有一个副本。我怀疑，使用连接的更新语句不包含在SQLite中，因为它们功能强大，但有些风险。

Using the pattern above you end up with two country fields. You can avoid that by explicitly stating all of the columns you want to retrieve from destination_old and perhaps using coalesce to retrieve the values from destination_old if the country field in source is null. So for example:

使用上面的模式，您将得到两个国家字段。可以通过显式地声明希望从destination_old中检索的所有列，或者使用coalesce从destination_old中检索值(如果源代码中的country字段为null)来避免这种情况。举个例子:

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;

#1

105

INSERT INTO Destination SELECT * FROM Source;