[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]
-----------------------------------------------------------------------------
