在 SQLite 中使用 Join 进行更新 [英] Update with Join in SQLite

问题描述

我有 2 个表，想用另一个表的值更新其中一个.

I have 2 tables and like to update one of them with the values from the other.

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

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

我已经尝试过这些查询，但是，SQLite 不支持 JOINS 与 UPDATE.任何可以提出查询的人.感谢您的帮助.

I've tried these queries but, SQLite doesn't support JOINS with UPDATE.anybody out there who can come up with a query for this.thanks for your help.

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 

推荐答案

这会起作用

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.

Here we use exists because without that the query will set software.purchprice to null if no "correlated" row is found.

这篇关于在 SQLite 中使用 Join 进行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！