How to check id-s of parents and then set value

2019-09-02 12:58发布

问题:

I have table like this :

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

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 :

step 1. select object_id , parent_id from myTbl where allowed = 1 Displays:

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

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.

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:

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

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

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.

Try:

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