SQLite inner join - update using values from another table
Solution 1
Using the update statement it is not possible because in sqlite joins in an update statement are not supported. See docs: update statement
If you only wanted to update a single column to a static value, you could use a subquery in the update statement correctly. See this example: How do I make an UPDATE while joining tables on SQLite?
Now in your example, making an assumption that there is a unique key on "column f" - a workaround/solution I have come up with is using the replace statement:
replace into table2
(a, b, c, d, e, f, g)
select src.a, src.b, src.c, src.d, src.e, dest.f, dest.g
from table1 src
inner join table2 dest on src.f = dest.f
I also added an extra column to table2 "column g" to show how you'd "update" only some of the columns with this method.
One other thing to be cautious about is if you use "PRAGMA foreign_keys = ON;" it's possible to have issues with this as the row is effectively deleted and inserted.
Solution 2
I came up with an alternative technique using a TRIGGER and "reversing" the direction of the update, albeit at the cost of a dummy field in the source table.
In general terms, you have a Master
table and an Updates
table. You want to update some/all fields of records in Master
from the corresponding fields in Updates
linked by a key field Key
.
Instead of UPDATE Master SET ... FROM Master INNER JOIN Updates ON Mater.Key = Updates.Key
you do the following:
Add a dummy field
TriggerField
to theUpdates
table to act as the focus of the trigger.-
Create a trigger on this field:
CREATE TRIGGER UpdateTrigger AFTER UPDATE OF TriggerField ON Updates BEGIN UPDATE Master SET Field1 = OLD.Field1, Field2 = OLD.Field2, ... WHERE Master.Key = OLD.Key END;
-
Launch the update process with the following:
UPDATE Updates SET TriggerField = NULL ;
Notes
The dummy field is merely an anchor for the trigger so that any other
UPDATE Updates SET ...
won't trigger the update intoMaster
. If you only everINSERT
intoUpdates
then you don't need it (and can remove theOF TriggerField
clause when creating the trigger).From some rough-and-ready timings, this seems to work about the same speed as
REPLACE INTO
but avoids the feels-slightly-wrong technique of removing and adding rows. It is also simpler if you are only updating a few fields inMaster
as you only list the ones you want to change.-
It is orders of magnitude faster than the other alternative I've seen to
UPDATE ... FROM
which is:UPDATE Master SET Field1 = ( SELECT Field1 FROM Updates WHERE Mater.Key = Updates.Key ), Field1 = ( SELECT Field1 FROM Updates WHERE Mater.Key = Updates.Key ), ... ;
Updating six fields over 1700 records was roughly 0.05s for Tony and my methods but 2.50s for the
UPDATE ... ( SELECT... )
method. AFTER UPDATE
triggers onMaster
seem to fire as expected.
Solution 3
As Tony says, the solution is the replace into way but you can use the sqlite hidden field rowid to simulate full update with join like:
replace into table2
(rowid,a, b, c, d, e, f, g)
select dest.rowid,src.a, src.b, src.c, src.d, src.e, dest.f, dest.g
from table1 src
inner join table2 dest on src.f = dest.f
With this you recreate full rows if you don't have primary key for the replace or as standard method to do the updates with joins.
Solution 4
SQLITE does not support UPDATE with INNER JOIN nor do several other DB's. Inner Joins are nice and simple however it can be accomplished using just a UPDATE and a subquery select. By using a where clause and the 'IN' with a subquery and a additional subquery for the 'SET' the same result can always be accomplished. Below is how it's done.
UPDATE table2
SET a = a + (select a from table1 where table1.f = table2.f),
b = b + (select b from table1 where table1.f = table2.f),
c = c + (select c from table1 where table1.f = table2.f),
d = d + (select d from table1 where table1.f = table2.f),
e = e + (select e from table1 where table1.f = table2.f)
WHERE RowId IN (Select table2.RowId from table1 where table1.f = table2.f)
navgeet
Updated on October 01, 2020Comments
-
navgeet over 3 years
This is quite easy and has been asked multiple times but I can't get it to work. The SQL query I think should work is:
UPDATE table2 SET dst.a = dst.a + src.a, dst.b = dst.b + src.b, dst.c = dst.c + src.c, dst.d = dst.d + src.d, dst.e = dst.e + src.e FROM table2 AS dst INNER JOIN table1 AS src ON dst.f = src.f
-
navgeet almost 12 yearsAfter searching everywhere, I too believe SQLite doesn't support joins in an update statement. I ended up using multiple subqueries. See stackoverflow.com/questions/3845718/…
-
Tony Gibbs almost 12 yearsThis doesn't work on the version of sqlite (SQLite 3.7.13 2012-06-11 02:05:22) I am testing it against. "Error: near "FROM": syntax error" Looking at the sqlite docs it doesn't look like you can have a from statement in an update query.
-
simo.3792 over 9 yearsi think your last row is mis-typed. table2.RowId should be table1.RowId or just RowId.