CODEWITHSUNDEEP
sqlitejoinsql-update
Johannes
Johannes

Reputation: 1064

Updating single specified values from another table in SQLite

I have two SQLite tables A and B defined as:

CREATE TABLE A (orig_cat INTEGER, type INTEGER,gv_ID INTEGER);
INSERT INTO A (orig_cat,type) VALUES (1,1);
INSERT INTO A (orig_cat,type) VALUES (2,2);
INSERT INTO A (orig_cat,type) VALUES (3,2);
INSERT INTO A (orig_cat,type) VALUES (4,2);
INSERT INTO A (orig_cat,type) VALUES (1,3);
INSERT INTO A (orig_cat,type) VALUES (2,3);
INSERT INTO A (orig_cat,type) VALUES (3,3);
UPDATE A SET gv_ID=rowid+99;

and 

CREATE TABLE B (col_t INTEGER, orig_cat INTEGER, part INTEGER);
INSERT INTO B VALUES (1,1,1);
INSERT INTO B VALUES (3,1,2);
INSERT INTO B VALUES (2,2,1);
INSERT INTO B VALUES (1,2,2);
INSERT INTO B VALUES (3,3,1);
INSERT INTO B VALUES (4,3,2);

I'd like to update and set/replace the values in column col_t of table B where part=2 with selected values of column gv_ID of table A. The selected values I can get with a SELECT command:

SELECT gv_ID 
FROM (SELECT * FROM B where part=2) AS B_sub 
JOIN (SELECT * FROM A WHERE type=3) AS A_sub 
  ON B_sub.orig_cat=A_sub.orig_cat;

How can I use that so that the values of col_t in rows 2,3 and 5 (=1,2,3) get replaced with the values 104,105,106 (wich is returned by the selection)?

Upvotes: 1

Views: 44

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175686

You can use correlated subquery:

UPDATE B
SET col_t = (SELECT gv_ID FROM A WHERE A.orig_cat = B.orig_cat AND A.type = 3)
WHERE B."part" = 2;

SqlFiddleDemo

I've assumed that pair A.orig_cat and A.type is UNIQUE.

Upvotes: 1

Related Questions