Update with Join in SQLite

Solution 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.

Solution 2:

You have to look up the corresponding values with a correlated subquery:

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

Solution 3:

This statement will work fine!

It will update only rows in 'software' that have the same ID in 'softwarecost'!

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

One more way to do it:

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

...this way is more convenient if you have to update all columns (if you have more columns to update)