SQLite内部联接-使用另一个表中的值进行更新 [英] SQLite inner join - update using values from another table

问题描述

这很容易，已被多次询问，但我无法正常工作. 我认为应该工作的SQL查询是:

This is quite easy and has been asked multiple times but I can't get it to work. The SQL query I think should work is:

    UPDATE table2
       SET dst.a = dst.a + src.a,
           dst.b = dst.b + src.b,
           dst.c = dst.c + src.c,
           dst.d = dst.d + src.d,
           dst.e = dst.e + src.e
      FROM table2 AS dst 
INNER JOIN table1 AS src
        ON dst.f = src.f

推荐答案

无法使用update语句，因为在sqlite中不支持update语句中的联接.查看文档: 更新声明

Using the update statement it is not possible because in sqlite joins in an update statement are not supported. See docs: update statement

如果只想将单个列更新为静态值，则可以在update语句中正确使用子查询.请参见以下示例:如何进行更新在SQLite上联接表时?

If you only wanted to update a single column to a static value, you could use a subquery in the update statement correctly. See this example: How do I make an UPDATE while joining tables on SQLite?

现在在您的示例中，假设列f"上有一个唯一键-我想出的解决方法/解决方案是使用replace语句:

Now in your example, making an assumption that there is a unique key on "column f" - a workaround/solution I have come up with is using the replace statement:

replace into table2
(a, b, c, d, e, f, g)
select src.a, src.b, src.c, src.d, src.e, dest.f, dest.g
from table1 src
inner join table2 dest on src.f = dest.f

我还向table2"g列"添加了一个额外的列，以显示您如何使用此方法仅更新"某些列.

I also added an extra column to table2 "column g" to show how you'd "update" only some of the columns with this method.

要注意的另一件事是，如果您使用"PRAGMA foreign_keys = ON;"，由于该行已被有效删除和插入，因此可能会出现问题.

One other thing to be cautious about is if you use "PRAGMA foreign_keys = ON;" it's possible to have issues with this as the row is effectively deleted and inserted.

这篇关于SQLite内部联接-使用另一个表中的值进行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！