I actually do create the new row on the "newdb" database, then attach it to the "main" one. Sorry if my notation was not clear.
I'm not getting a "no such column" error - I do get a result - but it's not the one that I'm expecting. I suspect I have to accomplish what I need in code. Cheers, Demitri On Thursday, May 12, 2005, at 01:12PM, Jay Sprenkle <[EMAIL PROTECTED]> wrote: >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 >>
