SQLite Forum

Can I do this as one liner?
Login

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! 😉 )

(4) By jake on 2020-03-25 12:14:25 in reply to 2 [link] [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.

(3) By Mark Lawrence (mark) on 2020-03-25 12:10:55 in reply to 1 [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.