Hi Guys,
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.e. I have:
Items
ID ParentId Name Flags
1 - Root
0
2 1 AAA
1
3 1 BBB
2
4 3 CCC
4
5 4 DDD
8
And I want to be able to propagate the bitflags up the table so that any
parent item will contain the child items bitmask:
Items
ID ParentId Name Flags
1 - Root
15
2 1 AAA
1
3 1 BBB
14
4 3 CCC
12
5 4 DDD
8
What I get is:
ID ParentId Name Flags
1 - Root
1
2 1 AAA
1
3 1 BBB
14
4 3 CCC
12
5 4 DDD
8
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))
Ideally I'd like to aggregate the SELECT Bitflags into one Bitflag and
OR it with the appropriate item.
Any idea how to do this in SQL? The only way I can think of getting this
to work properly is to pull the data out the DB and handle it in code.
Regards
Tristan Rhodes
Cotoco Ltd
Winners of e-Business Innovation Awards two years in a row.
For more information go to <http://www.cotoco.com
<http://www.cotoco.com/> >
Tel: +44 (0) 870 748 1400
Fax: +44 (0) 870 748 1411
******************************************************************
cotoco ltd
Registered in the UK, number 2986223
Registered Office: HTEC House, Southampton International Business Park,
George Curl Way, Southampton, Hampshire, UK, SO18 2RX
The contents of this email and any files transmitted with it may contain
proprietary information which may be confidential and/or legally
privileged. If you have received this email in error, please notify the
sender by replying to this email and then delete it from your system. If
you are not the intended recipient, you are not authorised to disclose,
copy, distribute or retain this email or any part of it. The views
expressed in this email are not necessarily the views of cotoco. Neither
cotoco nor the sender accepts any liability or responsibility for
viruses or other destructive elements and it is your responsibility to
scan for any attachments. Thank you for your co-operation.
******************************************************************
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
