Kit <kit.sa...@gmail.com> wrote: > Hi there, > > I have single table: > CREATE TABLE booklist(author TEXT, book TEXT); > --------------- > Tolkien The Hobbit > Hemingway The Old Man and the Sea > Verne Twenty Thousand Leagues under > Hemingway A Farewell to Arms > Tolkien The Lord of the Rings > ---------------- > I want split it into 2 tables: > CREATE TABLE authors(author TEXT); > CREATE TABLE books(authorID INT, book TEXT); > CREATE INDEX i_authors ON authors (author); > INSERT INTO authors SELECT DISTINCT author FROM booklist; > INSERT INTO books (authorID, book) > SELECT authors.rowid, booklist.book FROM authors,booklist > WHERE authors.author=booklist.author; > ----------------- > The algorithm run fine (maybe you know better), but when I want insert > next booklist with one the same author, author will be added twice. > Create unique index on authors block all adding rows.
You could do this: INSERT INTO authors SELECT DISTINCT author FROM booklist where author not in (select author from authors); Or else, create the unique index on authors.author as originally planned, then use INSERT OR IGNORE to skip over errors. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
