SQL - 使用临时表更新主表

I have a question about SQL, especially SQLite3. I have two tables, let's name them main_table and temp_table. These tables are based on the same relational schema so they have the same columns but different rows (values).

我有一个关于SQL的问题,尤其是SQLite3。我有两个表,让我们将它们命名为main_table和temp_table。这些表基于相同的关系模式,因此它们具有相同的列但行(值)不同。

Now what I want to do:

现在我想做什么:

For each row of the main_table I want to replace it if there is a row in a temp_table with the same ID. Otherwise I want to keep the old row in the table.

对于main_table的每一行,如果temp_table中有一行具有相同的ID,我想替换它。否则我想保留表中的旧行。

I was thinking about using some joins but it does not provides the thing I want.

我正在考虑使用一些连接,但它没有提供我想要的东西。

Would you give me an advice?

你能给我一个建议吗?

EDIT: ADITIONAL INFO:

编辑:附加信息:

I would like to avoid writing all columns because those tables conains tens of attributes and since I have to update all columns it couldn't be necessary to write out all of them.

我想避免编写所有列,因为这些表具有数十个属性,因为我必须更新所有列,所以没有必要写出所有列。

2 个解决方案

#1

If the tables have the same structure, you can simply use SELECT *:

如果表具有相同的结构,您只需使用SELECT *:

BEGIN;

DELETE FROM main_table
WHERE id IN (SELECT id
             FROM temp_table);

INSERT INTO main_table
SELECT * FROM temp_table;

COMMIT;

(This will also add any new rows in temp_table that did not previously exist in main_table.)

(这也将在temp_table中添加以前在main_table中不存在的任何新行。)

#2

You have 2 approaches:

你有两种方法:

Update current rows inside main_table with data from temp_table. The relation will be based by ID.

使用temp_table中的数据更新main_table中的当前行。该关系将基于ID。
Add a column to temp_table to mark all rows that have to be transferred to main_table or add aditional table to store IDs that have to be transferred. Then delete all rows that have to be transferred from table main_table and insert corresponding rows from temp_table using column with marks or new table.

向temp_table添加一列以标记必须传输到main_table的所有行,或添加aditional表以存储必须传输的ID。然后删除必须从表main_table传输的所有行,并使用带有标记或新表的列从temp_table插入相应的行。

#1