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
