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

Related for DELETE: https://stackoverflow.com/questions/24511153/how-delete-table-inner-join-with-other-table-in-sqlite — Ciro Santilli OurBigBook.com, Nov 03 '21 at 20:18

score 95 · Answer 1 · answered Jan 12 '14 at 12:33

95

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.

answered Jan 12 '14 at 12:33

Durai Amuthan.H

31,670
10
160
241

17

Since v3.33 (release 14th August 2020) sqlite supports an UPDATE FROM syntax: https://www.sqlite.org/lang_update.html#upfrom – Pelle Jacobs Nov 19 '20 at 14:09

score 47 · Answer 2 · answered Oct 09 '13 at 11:49

47

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

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

answered Oct 09 '13 at 11:49

CL.

173,858
17
217
259

7

But beware that this will set `software.purchprice` to `null` where no "correlated" row can be found in `softwarecost`. – Colin 't Hart Oct 09 '13 at 12:09
4

@Colin'tHart That seems like the desired behavior, no? – Ari B. Friedman Nov 13 '13 at 17:32
10

I just tried this. the field purchprice in entire table software will have the same value. it doesn't do a join. – mcfly soft Aug 07 '14 at 05:54
SQLite3 doesn't seem to support `WHERE`statements referring to other tables in the db (I am running a similar code to above and get the error `Error: no such column: software.id`). Any clues or should I open a new question? – lmrta Jun 19 '17 at 14:22
@lmrta You can refer only to tables used by the UPDATE or by a SELECT. – CL. Jun 19 '17 at 14:42

score 16 · Answer 3 · edited Aug 16 '23 at 09:17

16

Using the new UPDATE FROM syntax, which was introduced in v3.33 (release 14th August 2020) as suggested by Pelle Jacobs in a comment.

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

edited Aug 16 '23 at 09:17

Aliostad

80,612
21
160
208

answered Nov 03 '21 at 20:48

Jan Derk

2,552
27
22

Note that "update from" is supported since version 3.33 – Gad D Lord Dec 28 '22 at 22:37

Vasilii P · Answer 4 · 2015-03-15T19:40:22.943

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)

score 6 · Answer 5 · answered Feb 28 '20 at 19:31

6

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.

answered Feb 28 '20 at 19:31

Eric Woltermann

99
1
3

Where do you get "excluded." from? – WillD Jun 13 '20 at 22:06
1

It is a reserved pseudo-table, which is automatically made available by the UPSERT syntax. See the linked SQLite doc: "Column names in the expressions of a DO UPDATE refer to the original unchanged value of the column, before the attempted INSERT. To use the value that would have been inserted had the constraint not failed, add the special "excluded." table qualifier to the column name." – Eric Woltermann Jun 17 '20 at 18:41

score 5 · Answer 6 · answered Feb 27 '21 at 00:55

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)

Vic Fanberg · Answer 7 · 2019-11-20T15:12:01.530

4

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.

edited Nov 20 '19 at 15:12

answered Nov 20 '19 at 14:49

Vic Fanberg

983
1
7
14

This solution is shorter and doesn't repeat the query. But, I tested both and this one is, for some reason, orders of magnitude slower than the one with "WHERE EXISTS". So, only to use if speed is of no consequence. – Julen Feb 11 '20 at 17:47
That would actually make sense if NULLs were rare in column purchprice and the table was very large because SQL has to do a lot of searching for values when it doesn't need to. So it is dependent on your data set on whether you anticipate mostly NULLs or mostly values already filled in. If you have mostly already filled in values and the table is huge, you are taking a hit using this format SQL. If the table is small, or mostly NULLs in the purchprice column, or maintainability is the primary concern of the SQL statement, then use this format SQL. – Vic Fanberg Feb 11 '20 at 22:10
I am going to add one more comment on this. In practice, there is usually some other condition to reduce the set of of values that the update statement would look at, such as all records that have been created today or particular batch numbers that would be specified in a where clause on the update statement itself to reduce the quantity of rows the update statement would look at. Then, it can be approximately as efficient as the WHERE EXISTS version, but is easier to maintain. – Vic Fanberg Feb 13 '20 at 14:35

Reginaldo Marcilon · Answer 8 · 2014-01-09T13:37:59.093

1

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.

edited Jan 09 '14 at 13:37

answered Jan 09 '14 at 11:17

Reginaldo Marcilon

64
8

score 0 · Answer 9 · edited Dec 14 '21 at 17:18

0

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

edited Dec 14 '21 at 17:18

v010dya

5,296
7
28
48

answered Oct 16 '21 at 14:03

kader hussain

67
1
1
4

score -2 · Answer 10 · edited Aug 24 '19 at 08:15

-2

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)

edited Aug 24 '19 at 08:15

Kiran Maniya

8,453
9
58
81

answered Aug 24 '19 at 06:59

user11970466

9

score -3 · Answer 11 · edited Nov 12 '21 at 10:11

-3

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

edited Nov 12 '21 at 10:11

Tony

9,672
3
47
75

answered Oct 09 '13 at 11:08

Bogdan Burym

5,482
2
27
46

Hi Bogdan thanks for your relply , I'm getting this error. SQLiteManager: Likely SQL syntax error: UPDATE software s INNER JOIN softwarecost sc on s.id=sc.id SET s.purchprice=sc.purchprice [ near "s": syntax error ] Exception Name: NS_ERROR_FAILURE – Carter Oct 09 '13 at 11:12

Update with Join in SQLite

11 Answers11

Linked

Related