Correct SQLite syntax - UPDATE SELECT with WHERE EXISTS

Question

I am trying to update a selected values in a column in a SQLite table. I only want update of the cells in the maintable where the criteria are met, and the cells must be updated to individual values, taken from a subtable.

I have tried the following syntax, but I get only a single cell update. I have also tried alternatives where all cells are updated to the first selected value of the subtable.

UPDATE maintable
SET value=(SELECT subtable.value FROM maintable, subtable
WHERE  maintable.key1=subtable.key1 AND maintable.key2=subtable.key2)
WHERE EXISTS (SELECT subtable.value FROM maintable, subtable
WHERE  maintable.key1=subtable.key1 AND maintable.key2=subtable.key2)

What is the appropriate syntax?

Answer 1

You can do this with an update select, but you can only do one field at a time. It would be nice if Sqlite supported joins on an update statement, but it does not.

Here is a related SO question, How do I UPDATE from a SELECT in SQL Server?, but for SQL Server. There are similar answers there.

sqlite> create table t1 (id int, value1 int);
sqlite> insert into t1 values (1,0),(2,0);
sqlite> select * from t1;
1|0
2|0
sqlite> create table t2 (id int, value2 int);
sqlite> insert into t2 values (1,101),(2,102);
sqlite> update t1 set value1 = (select value2 from t2 where t2.id = t1.id) where t1.value1 = 0;
sqlite> select * from t1;
1|101
2|102