SQLite Forum

Can I do this as one liner?
Login
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;
```