Reputation: 127
I need to "roll up" bit values for a group in my table via an UPDATE
. So if one row in the group has a bit set to true, I need to set other rows in the group to true.
For example, I have a table like this:
GroupID | Type | BitA | BitB
------------------------------
1 | Parent | 0 | 0
1 | Child | 1 | 0
2 | Parent | 0 | 0
2 | Child | 1 | 1
2 | Child | 0 | 1
3 | Parent | 0 | 0
3 | Child | 0 | 0
The expected result after the UPDATE
statement would then be:
GroupID | Type | BitA | BitB
------------------------------
1 | Parent | 1 | 0
1 | Child | 1 | 0
2 | Parent | 1 | 1
2 | Child | 1 | 1
2 | Child | 0 | 1
3 | Parent | 0 | 0
3 | Child | 0 | 0
Since Group 1 has a Child with BitA set to true, the Parent should also be set to true.
The only thing I've been able to find is casting the bit as an int first in order to aggregate it in the group, but it seems like I'm going down a rabbit hole.:
SELECT
MAX(CAST(BitA as int)),
MAX(CAST(BitB as int))
FROM MyTable
WHERE Type = 'Child'
GROUP BY GroupID
Is there an easier way to do this?
Upvotes: 1
Views: 55
Reputation: 129
this could give you what you need ...
UPDATE M1
SET M1.BITA = 1,
M1.BITB = 1
FROM MYTABLE M1
WHERE EXISTS
(
SELECT 1
FROM MYTABLE M2
WHERE M2.GroupID = M1.GoupID
AND
(
M2.BITA = 1
OR M2.BITB = 1
)
);
Upvotes: 0
Reputation: 1271231
You seem to want to set the parent flags based on the children:
with toupdate as (
select t.*,
max(BitA) over (partition by groupid) as max_bitA,
max(BitB) over (partition by groupid) as max_bitB
from mytable t
)
update toupdate
set BitA = max_BitA,
BitB = max_BitB
where BitA <> max_BitA or BitB <> max_BitB;
If your bits are really typed as bit
, you need some conversions:
with toupdate as (
select t.*,
convert(bit, max(convert(int, BitA)) over (partition by groupid)) as max_bitA,
convert(bit, max(convert(int, BitB)) over (partition by groupid)) as max_bitB
from mytable t
)
update toupdate
set BitA = max_BitA,
BitB = max_BitB
where BitA <> max_BitA or BitB <> max_BitB;
Upvotes: 2