如何检查父母的id-s然后设置值

2021-09-10 00:00:00 sql tsql sql-server-2008 sql-server

我有这样的桌子:

ID  object_id    parent_id   allowed
1       1           0           0   
2       23          25          1
3       25          44          0       
4       44          38          0       
5       38          1           0
6       52          55          1           
7       55          58          0
8       58          60          0
9       60          1           0

现在想要选择 row-s where allowed = 1 然后为我选择的行的父级设置 allowed = 1.例如它会像:

Now want select row-s where allowed = 1 and then set allowed = 1 for parents of the row which i select. For example it will be like :

步骤 1. select object_id , parent_id from myTbl where allowed = 1 显示:

ID  object_id    parent_id   allowed
2       23          25          1   
6       52          55          1

第 2 步:检查 object_id 是否是 IN 上面结果中的 parent_id 并设置 allowed = 1 当 object_id 等于任何 parent_id 时.

step 2: It checks if the object_id is IN the parent_id from the above result and sets allowed = 1 when the object_id is equal to any of the parent_id's.

重复完全相同的 step2，直到到达 object_id 和 parent_id

The exact same step2 repeats until it reaches a point where there is no match between object_id and parent_id

ID  object_id    parent_id   allowed
2       23          25          1   
6       52          55          1
3       25          44          0 --update to 1
7       55          58          0 -- update to 1

完全相同的原则也适用于以下记录:

The exact same principle is being applied to the folling records, too:

对于 25,44,1 - 44,38,0 (allowed is 0 want set 1) 当 set allowed = 1 时44,38,1

for 25,44,1 - 44,38,0 (allowed is 0 want set 1) when set allowed = 1 it will be 44,38,1

对于 55,58,1 - 58,60,0 (allowed is 0 want set 1) 当 set allowed = 1 时58,60,1

for 55,58,1 - 58,60,0 (allowed is 0 want set 1) when set allowed = 1 it will be 58,60,1

怎么做?在表中，我的表包含多个状态为 allowed=1 的记录，并且在此特定示例中仅使用了其中的 2 个.

How to do it ? In table My table contains multiple records with status allowed=1 and only 2 of them are used in this particular example.

推荐答案

尝试:

UPDATE tbl 
SET    allowed = 1 
FROM   (SELECT * 
        FROM   tbl 
        WHERE  allowed = 0) A 
        INNER JOIN 
        (SELECT * 
            FROM   tbl                 
            WHERE  allowed = 1) B 
        ON A.objectid = B.parentid

相关文章