Update table by all records from second table

Question

Morning,

I have two tables. The first table (SecurityRules) is a list of security rules:

ID  srRight srRole
1   4       NULL    
2   2       32

The second table (Projects) is a list of Projects :

ProjId  prRight prRole
1       0       NULL
2       0       32
3       0       NULL

I need to update the list of projects with all the records from SecurityRules and update the prRight column based on the Role from both tables. The Right values are bitwise-organised. I used the following SQL update query to do this:

Update  Projects
        -- Perform binary sum
Set     prRight = prRight | srRight
From    SecurityRules
Where   (srRole is Null)                            --Always apply srRight if srRole is not defined
        OR (srRole is Not Null And srRole=prRole)   --Apply right if both roles are equal

The expected result is:

ProjId  prRight prRole
1   4   NULL
2   6   32
3   4   NULL

But I get:

ProjId  prRight prRole
1   4   NULL
2   4   32
3   4   NULL

It looks like the update is done only by the first record of the SecurityRules table. And I need to apply all the records from the SecurityRules table to all records of the Project table. If I create a simple loop and manually looped all the records from SecurityRules it is working fine, but the performance is very poor if you have to compare 10 security rules to 2000 projects...

Any suggestion?

Arno

score 2 · Accepted Answer · answered Jan 18 '20 at 08:20

This answer is based on the code in this answer for generating a bitwise OR of values. It uses CTEs to generate a bit mask for each rights value and then the overall bitwise OR by summing the distinct bit masks present in each of the rights values. The output of the last CTE is then used to update the Projects table:

WITH Bits AS (
  SELECT 1 AS BitMask
  UNION ALL
  SELECT 2 * BitMask FROM Bits
  WHERE BitMask < 65536
),
NewRights AS (
  SELECT ProjId, SUM(DISTINCT BitMask) AS NewRight
  FROM Projects p
  JOIN SecurityRules s ON s.srRole IS NULL OR s.srRole = p.prRole
  JOIN Bits b ON b.BitMask & s.srRight > 0
  GROUP BY ProjID
)
UPDATE p
SET p.prRight = n.NewRight
FROM Projects p
JOIN NewRights n ON n.ProjId = p.ProjId

Resultant Projects table:

ProjId  prRight     prRole
1       4           null
2       6           32
3       4           null

Demo on dbfiddle

Yep, this is working. The example is a simple representation of the actual situation where also the role is a bitwise-operator and by just replacing s.srRole = p.prRole by (s.srRole & p.prRole) >0 this directy works. Great. Thanks — Arno Voerman, Jan 20 '20 at 08:24

score 0 · Answer 2 · answered Jan 18 '20 at 13:05

If I understand correctly, you have a direct match on the srRole column and then a default rule that applies to everyone.

The simplest method (in this case) is to use joins in the update:

update p
    Set prRight = p.prRight | srn.srRight | coalesce(sr.srRight, 0)
From Projects p join
     SecurityRules srn
     on srRole is null left join
     SecurityRules sr
     on sr.srRole = p.prRole;

Here is a db<>fiddle.

You might be safer assuming no default rule. And that prRight could be NULL:

update p
    Set prRight = coalesce(p.prRight, 0) | coalesce(srn.srRight, 0) | coalesce(sr.srRight, 0)
From Projects p left join
     SecurityRules srn
     on srRole is null left join
     SecurityRules sr
     on sr.srRole = p.prRole;

That said, I would recommend that you consider revising your data model. Bit fiddling is a lot of fun in programming languages. However, it is generally not the best approach in databases. Instead use junction tables, unless your application has some real need for bit switches.

"Bit fiddling is a lot of fun in programming languages. However, it is generally not the best approach in databases". You're absolutely right. But I this is historically and can not be changed that easily. — Arno Voerman, Jan 20 '20 at 08:26

Update table by all records from second table

2 Answers2