Can I do this as one liner?
(1) By anonymous on 2020-03-25 11:30:48 [link]
Hello, I have a big table like this: ~~~tcl sqlite3 ::db ":memory:" ::db eval { create table tbl (type,name,value); insert into tbl values('A','name1','value1'); insert into tbl values('B','name1',''); insert into tbl values('A','name2','value2'); insert into tbl values('B','name2',''); } ~~~ Then I will update values. Is it possible to make this faster in one statement? ~~~tcl ::db eval {select type,name,value from tbl where type='A'} r { ::db eval {update tbl set value=$r(value) where type='B' and name=$r(name)} } ~~~ Thank you
(2) By luuk on 2020-03-25 11:53:10 in reply to 1 [link]
update t1 set t1.value=t2.value from tbl t1 inner join tbl2 t2 on t1.name=t2.name and t2.type='A' where t1.type='B' (untested! 😉 )
(4) By jake on 2020-03-25 12:14:25 in reply to 2
SQLite does not support joins in UPDATE queries as far as I know. The following methods should work (also untested): ```sql -- -- UPDATE with subqueries method -- UPDATE tbl SET value = (SELECT value FROM (SELECT type, name, value FROM tbl WHERE type = 'A') WHERE name = tbl.name) WHERE type = 'B' AND name IN (SELECT name FROM tbl WHERE type = 'A'); -- -- INSERT OR REPLACE method -- -- Requires UNIQUE constraint on (type, name). -- Will DELETE old record then INSERT a new record -- REPLACE INTO tbl( type, name, value ) SELECT 'B', name, value FROM tbl WHERE type = 'A'; -- -- CTE method (3.8.3+) -- WITH A AS ( SELECT type, name, value FROM tbl WHERE type = 'A' ) UPDATE tbl SET value = (SELECT value FROM A WHERE name = tbl.name) WHERE type = 'B' AND name IN (SELECT name FROM A); -- -- UPSERT method (3.24.0+) -- -- Requires UNIQUE constraint on (type, name) -- INSERT INTO tbl( type, name, value ) SELECT 'B', name, value FROM tbl WHERE type = 'A' ON CONFLICT(type, name) DO UPDATE SET value = excluded.value; ```
(5) By anonymous on 2020-03-25 14:57:40 in reply to 4 [link]
Thank you Jake. The UPDATE part is working.
(3) By Mark Lawrence (mark) on 2020-03-25 12:10:55 in reply to 1 [link]
I would look into the UPSERT feature. That allows you to write SELECT statements from whatever tables you like, and feed the results into an INSERT where you can specify ON CONFLICT DO UPDATE to modify rows instead.