[disclaimer: I haven't touched SQL since my academic years...] I am troubled by a problem concerning efficiency; I'll present a simplified case.
I have a catalog of people, with their parents' (first) names: create table cat (id integer, last text, first text, dad text, mom text); I'd like to "factor out" the first names of people (and their parents), i.e. just keep an id to another table of names: create table newcat (id integer, last text, first integer, dad integer, mom integer); With my (limited) SQL knowledge, I attempted: -- table of first names create table firstnames (id integer, name text); create index idx_f on firstnames(name collate nocase); -- collect all first names in a (temporary table) create temporary table temp_names(name text); insert into temp_names select distinct first from cat; insert into temp_names select distinct mom from cat; insert into temp_names select distinct dad from cat; -- one of each back in the table insert into firstnames select distinct null as id, name from temp_names; -- and now, fill the new table changing names into id's. insert into newcat select id, last, f_first.id as first, f_dad.id as dad, f_mom.id as mom from cat, firstnames as f_first, firstnames as f_dad, firstnames as f_mom, where cat.first = f_first.name and cat.dad = f_dad.name and cat.mom = f_mom.name ; Question: is this the most efficient way of doing it ? It's taking forever... Many thanks in advance for any insight -- zvr - -- View this message in context: http://www.nabble.com/how-to-efficiently-transform-a-table--tf4732328.html#a13531786 Sent from the SQLite mailing list archive at Nabble.com. ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------