Correct SQLite Syntax - UPDATE SELECT with WHERE EXISTS

Question

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

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

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 update select , but you can only do one field at a time. It would be nice if Sqlite supported combining in update instructions, but it is not.

Here is a related SO question, How do I UPDATE from 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 

Answer 2

You need to use the INSERT OR REPLACE statement, for example:

Suppose the maintable has 4 columns: key, col2, col3, col4
and you want to update col3 with the appropriate value from the subtable

 INSERT OR REPLACE INTO maintable SELECT maintable.key, maintable.col2, subtable.value, maintable.col4 FROM maintable JOIN subtable ON subtable.key = maintable.key 

Answer 3

We can use with-clause + column-name-list + select-stmt from https://www.sqlite.org/lang_update.html to do something like this:

 CREATE TABLE aa ( _id INTEGER PRIMARY KEY, a1 INTEGER, a2 INTEGER); INSERT INTO aa VALUES (1,10,20); INSERT INTO aa VALUES (2,-10,-20); --a bit unpleasant because we have to select manually each column and it just a lot to write WITH bb (_id,b1, b2) AS (SELECT _id,a1+2, a2+1 FROM aa) UPDATE aa SET a1=(SELECT b1 FROM bb WHERE bb._id=aa._id),a2=(SELECT b2 FROM bb WHERE bb._id=aa._id) WHERE a1<=1000 OR a2<=2000; --totally useless where clause but just so you don't update every row in aa by putting this where clause in the bb --soo now it should be (1,10,20)->(1,12,21) and (2,-10,-20)->(2,-8,-19), and it is SELECT * FROM aa; --even better with one select for each row! WITH bb (_id,b1, b2) AS (SELECT _id,a1+2, a2+1 from aa) UPDATE aa SET (_id,a1,a2)=(SELECT bb._id,b1,b2 FROM bb WHERE bb._id=aa._id) WHERE a1<=1000 OR a2<=2000; --totally useless where clause but just so you don't update every row in aa by putting this where clause in the bb --soo now it should be (1,12,21)->(1,14,22) and (2,-8,-19)->(2,-6,-18), and it is SELECT * FROM aa; --you can skip the with altogether UPDATE aa SET (_id,a1,a2)=(SELECT bb._id,bb.a1+2, bb.a2+1 FROM aa AS bb WHERE aa._id=bb._id) WHERE a1<=1000 OR a2<=2000; --totally useless where clause but just so you don't update every row in aa by putting this where clause in the bb --soo now it should be (1,14,22)->(1,16,23) and (2,-4,-17)->(2,-6,-18), and it is SELECT * FROM aa; 

I hope sqlite is smart enough not to perform step-by-step queries, but according to the documentation it is.

Answer 4

In this case, it updates only one value from subtable for each raw from maintable. The error is that subtable is included in the SELECT clause.

UPDATE maintable SET value = (SELECT subtable.value FROM THE SUBTITLE WHERE maintable.key1 = subtable.key1);