mercredi 2 décembre 2015

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)?

Aucun commentaire:

Enregistrer un commentaire