Can I do this as one liner?
(1) By anonymous on 2020-03-25 11:30:48 [link] [source]
Hello,
I have a big table like this:
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?
::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] [source]
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! 😉 )
(3) By Mark Lawrence (mark) on 2020-03-25 12:10:55 in reply to 1 [link] [source]
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.
(4) By jake on 2020-03-25 12:14:25 in reply to 2 [source]
SQLite does not support joins in UPDATE queries as far as I know. The following methods should work (also untested):
--
-- 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] [source]
Thank you Jake. The UPDATE part is working.