SQLITE UPDATE alternative

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 INNER JOIN softwarecost on software.id=softwarecost.id SET software.purchprice=softwarecost.purchprice 

Answer 1

I think you want what we call a correlated update:

update software
set purchprice = (select purchprice from softwarecost where id = software.id);

But beware that this will set purchprice to null for each row in software where no matching row can be found in softwarecost.