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