I believe your sql is wrong: There is no 'new_id' in attached.keyword. The alter table was applied to the other table.
UPDATE attached.keyword SET new_id = keyword.id WHERE label = keyword.label; On 5/11/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > 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 > -- --- You a Gamer? If you're near Kansas City: Conquest 36 https://events.reddawn.net The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264
