問題描述
我有這樣的桌子:
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
現(xiàn)在想要選擇 row-s where allowed = 1 然后為我選擇的行的父級設(shè)置 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 上面結(jié)果中的 parent_id 并設(shè)置 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.
重復(fù)完全相同的 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
怎么做?在表中，我的表包含多個狀態(tài)為 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
這篇關(guān)于如何檢查父母的id-s然后設(shè)置值的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！