Update with Join in SQLite

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 

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.

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

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

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)

I just found this one, using an UPSERT:

INSERT INTO software (id, purchprice)
SELECT a.id, b.purchprice FROM software AS a INNER JOIN softwarecost AS b ON a.id=b.id
ON CONFLICT(id) DO UPDATE SET purchprice=excluded.purchprice

This only works if you have

• SQLite version 3.24.0 or above and
• a UNIQUE constraint on software.id, e.g. by defining it as primary key.

It does not need potentially slow correlated subqueries and can handle multiple column updates.

I know this is old, but I prefer

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

It works as a general solution to the UPDATE with JOINs problem and saves SQLite having to execute the second SELECT statement to verify there actually is a row in the subquery.

The other answers here are mostly correct but one slight change is needed. At least I certainly needed to make a change for it to work. Not sure if it was a change to SQLite that's occurred over the years or just that it was never caught here, but you need to fully qualify the reference to column id from softwarecost within the WHERE clause to be WHERE softwarecost.id = software.id instead of just WHERE id = software.id. Without doing this the query I just ran made all entries the same value and it was the first value that linked - meaning all entries of software.purchprice became the same as the first entry in softwarecost.purchprice. This, I presume, is due to the id column being ambiguous if it is just "id=software.id" and thus links on itself.

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

Using the new UPDATE FROM syntax as suggested by Pelle Jacobs in a comment.

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

Carter, the problem with >>near "s": syntax error<< is about the using of the ALIAS. The UPDATE command don't accept ALIAS. If you try bogdan tip about REPLACE, you can do the join into the SELECT clause.

Update software 
  set Column1 = anyValue
  where id in (Select sf.id from software sf join softwarecost  sfc on sf.id=sfc.id
    where sfc.purchprice='anyValue' 
  )

If you what to Update with Join and Where Condition

You can try as given,

UPDATE software SET purchprice = (SELECT purchprice FROM softwarecost a WHERE a.id =software.id) where purchprice IN(SELECT distinct(purchprice) FROM softwarecost a WHERE a.id = software.id)

There is no JOIN here:

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

This will not work:

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

SQLite really does not support JOIN in UPDATE queries http://sqlite.org/lang_update.html

What about using REPLACE?
Maybe you could:

REPLACE INTO software
SELECT id, purchprice
FROM softwarecost