在SQLite中使用Join更新

Question

我有两个表，喜欢用另一个表的值更新其中一个表。

software
---------
id ,
purchprice

softwarecost
------------
id ,
purchprice

我已经尝试过这些查询，但SQLite不支持使用UPDATE进行连接。任何人都可以提出查询。谢谢您的帮助。

UPDATE software 
SET software.purchprice=softwarecost.purchprice 
WHERE software.id=softwarecost.id

UPDATE software 
INNER JOIN softwarecost on software.id=softwarecost.id 
SET software.purchprice=softwarecost.purchprice 

Answer 1

这种说法行得通！

它将只更新软件中与softwarecost中具有相同ID的行！

UPDATE software SET software.purchprice = 
     (SELECT purchprice FROM softwerecost WHERE software.id = softwerecost.id) 
     WHERE id IN (SELECT id FROM softwarecost);

还有一种方法可以做到：

DELETE FROM software WHERE id IN (SELECT id FROM softwarecost);
INSERT INTO software SELECT * FROM softwarecost;

…如果必须更新所有列（如果有更多列要更新），则此方法更方便

Answer 2

您必须使用相关子查询查找相应的值：

UPDATE software
SET purchprice = (SELECT purchprice
                  FROM softwarecost
                  WHERE id = software.id)

Answer 3

这会管用的

UPDATE 
      software
SET purchprice = (SELECT purchprice
                  FROM softwarecost
                  WHERE id = software.id) 
where EXISTS (SELECT purchprice
                  FROM softwarecost
                  WHERE id = software.id)

这里我们使用exists，因为如果没有它，查询将在没有找到“相关”行的情况下将software.purchprice设置为null。