By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE SecurityRules
([ID] int, [srRight] int, [srRole] varchar(4))
;
INSERT INTO SecurityRules
([ID], [srRight], [srRole])
VALUES
(1, 4, NULL),
(2, 2, '32')
;
CREATE TABLE Projects
([ProjId] int, [prRight] int, [prRole] varchar(4))
;
INSERT INTO Projects
([ProjId], [prRight], [prRole])
VALUES
(1, 0, NULL),
(2, 0, '32'),
(3, 0, NULL)
;
5 rows affected
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
3 rows affected
select *
from Projects
| ProjId | prRight | prRole |
|---|---|---|
| 1 | 4 | null |
| 2 | 6 | 32 |
| 3 | 4 | null |