Clemens,
This is wonderful... I have no clue how it works, but it works.
this "w2" table you are using, what is this and where do you get it from?
In any case, thanks for your help.
gert
2012/10/31 Clemens Ladisch <clem...@ladisch.de>
> Gert Van Assche wrote:
> > I have a table like this one:
> >
> > CREATE TABLE [WORLD] ([Continent] , [Region] , [Country] , [TreeNode]
> CHAR);
> > insert into [WORLD] values('America', null, null, '1');
> > insert into [WORLD] values('America', 'North', null, '1.1');
> > insert into [WORLD] values('America', 'Central', null, '1.2');
> > insert into [WORLD] values('America', 'South', null, '1.3');
> > insert into [WORLD] values('Europe', null, null, '2');
> > insert into [WORLD] values('Europe', 'West', null, '2.1');
> > insert into [WORLD] values('Europe', 'Central', null, '2.2');
> > insert into [WORLD] values('Europe', 'East', null, '2.3');
> > insert into [WORLD] values('America', 'North', 'USA', '1.1.1');
> > insert into [WORLD] values('America', 'Central', 'Honduras', '1.2.1');
> > insert into [WORLD] values('America', 'South', 'Peru', '1.3.1');
> > insert into [WORLD] values('America', 'South', 'Brasil', '1.3.2');
> > insert into [WORLD] values('Europe', 'West', 'Spain', '2.1.1');
> > insert into [WORLD] values('Europe', 'West', 'France', '2.1.2');
> > insert into [WORLD] values('Europe', 'Central', 'Romania', '2.2.1');
> > insert into [WORLD] values('Europe', 'East', 'Estonia', '2.3.1');
> > insert into [WORLD] values('Asia', 'East', 'Japan', '3.1.1');
> > insert into [WORLD] values('Asia', 'East', 'Korea', '3.1.2');
> > insert into [WORLD] values('Asia', 'West', 'India', '3.2.1');
> > insert into [WORLD] values('Africa', 'North', 'Egypt', '4.1.1');
> >
> > But I would like to generate the TreeNode fields automatically.
> > Is there a way I can do this in SQL?
>
> This data has no indication that the order of continents is
> America - Europe - Asia - Africa, or of the other columns' orderings.
>
> You could try something like this, which uses alphabetical order instead:
>
> UPDATE World
> SET TreeNode = CAST((SELECT COUNT(DISTINCT Continent)
> FROM World w2
> WHERE w2.Continent <= World.Continent) AS TEXT);
>
> UPDATE World
> SET TreeNode = TreeNode || '.' ||
> (SELECT COUNT(DISTINCT Region)
> FROM World w2
> WHERE w2.Continent = World.Continent AND
> w2.Region <= World.Region)
> WHERE Region IS NOT NULL;
>
> UPDATE World
> SET TreeNode = TreeNode || '.' ||
> (SELECT COUNT(DISTINCT Country)
> FROM World w2
> WHERE w2.Continent = World.Continent AND
> w2.Region = World.Region AND
> w2.Country <= World.Country)
> WHERE Region IS NOT NULL AND
> Country IS NOT NULL;
>
>
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
