SQLite 内连接 - 使用另一个表中的值进行更新

2022-01-17 00:00:00 join sql-update sql sqlite

这很简单，已被多次询问，但我无法让它发挥作用.我认为应该起作用的 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

推荐答案

使用更新语句是不可能的，因为在 sqlite 中不支持更新语句中的联接.请参阅文档:更新声明

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

如果您只想将单个列更新为静态值，您可以在更新语句中正确使用子查询.请参阅此示例:如何进行更新在 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"上有一个唯一键 - 我想出的解决方法/解决方案是使用替换语句:

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.

相关文章