Hi Igor, There are potentially a lot of bitflags, so I'm going to have to make a function, any good resources to get me started on this?
Tristan Rhodes -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: 14 April 2009 16:53 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Aggregating BitFlags Tristan Rhodes <tristan.rho...@cotoco.com> wrote: > I have a scenario where I am representing a tree in a table, standard > self relation ID/ParentId stuff. This tree contains bitflag data on > each item, and I want to be able to propagate the bitflag up to the > parent by repeatedly running a query that will bubble them up to the > next Item. However, the bitwise OR operation I am performing only > updates from a single bitflag, rather than all the child bitflags. > > I'm running this query: > > UPDATE Item SET Flags = Flags | (SELECT NestItem.Flags FROM Item AS > NestItem WHERE NestItem.ParentId = Item.Id) > > WHERE EXISTS > > (SELECT * FROM Item AS NestItem WHERE > NestItem.ParentId = Item.Id)) What you need is something like this: UPDATE Item SET Flags = Flags | coalesce( (SELECT AggregateOR(NestItem.Flags) FROM Item AS NestItem WHERE NestItem.ParentId = Item.Id), 0); The problem is, there is no built-in AggregateOR function you could use - I just made one up. If you only use a reasonably small number of bits, you could simulate it with this: Max(NestItem.Flags & 1) | Max(NestItem.Flags & 2) | Max(NestItem.Flags & 4) | Max(NestItem.Flags & 8) Otherwise, you would probably have to write a custom aggregate function. It should be fairly trivial. Igor Tandetnik _______________________________________________ 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
