Gert Van Assche wrote: > 2012/10/31 Clemens Ladisch <clem...@ladisch.de> >> UPDATE World >> SET TreeNode = CAST((SELECT COUNT(DISTINCT Continent) >> FROM World w2 >> WHERE w2.Continent <= World.Continent) AS TEXT); > > this "w2" table you are using, what is this and where do you get it from?
It's an alias for the World table. (For some reason, I tend to omit the AS from table aliases; think "FROM World AS w2".) "World.Continent" refers to the record in the World table that is currently being updated. The query over w2 is a correlated subquery; it counts how many continents there are the table before the current record. To understand it, assume that the UPDATE is currently handling an 'Asia' record, and consider the results of the following queries: SELECT Continent FROM World AS w2 WHERE w2.Continent <= 'Asia'; SELECT DISTINCT Continent FROM World AS w2 WHERE w2.Continent <= 'Asia'; SELECT COUNT(DISTINCT Continent) FROM World AS w2 WHERE w2.Continent <= 'Asia'; Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
