Hello, I'm wondering if SQLite supports an UPDATE/JOIN operation. Below is an example:
# DB "newdb" CREATE TABLE keyword (id INTEGER PRIMARY KEY, label); INSERT INTO keyword (id, label) VALUES (1, 'kappa'); INSERT INTO keyword (id, label) VALUES (2, 'gamma'); INSERT INTO keyword (id, label) VALUES (3, 'bravo'); ALTER TABLE keyword ADD COLUMN new_id INTEGER; VACUUM keyword; # DB "main" CREATE TABLE keyword (id INTEGER PRIMARY KEY, label); INSERT INTO keyword (id, label) VALUES (1, 'alpha'); INSERT INTO keyword (id, label) VALUES (2, 'bravo'); INSERT INTO keyword (id, label) VALUES (3, 'zeta'); # From "main": ATTACH DATABASE "newdb" AS attached; UPDATE attached.keyword SET new_id = keyword.id WHERE label = keyword.label; SELECT id, label, new_id FROM attached.keyword; The result is: 1|kappa|1 2|gamma|2 3|bravo|3 ...where I would have hoped it to be... 1|kappa| 2|gamma| 3|bravo|2 Is my SQL wrong or is this type of operation not supported? Cheers, Demitri
