Update with Join in SQLite

Question

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

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

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

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 

Answer 1

This will work

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

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