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
