CODEWITHSUNDEEP
sqlsql-serverparentcascade
Shadi
Shadi

Reputation: 57

Update a row and all 'parents' (if exists)

i need a help to do a query where i update a row initially based on the description and all parents, if exists.

My table ItemMenu:

Id

DescName

IdParent

BolActive

So, i need activate (set BolActive = 1) a row where description i'll inform in the query and then update all the rows if IdParent is not null/blank and if it exists in the same table (IdParent = Id).

Something like:

Update ItemMenu set BolActive = 1 where DescName = 'MyMenu'

then i need to check if IdParent from Select * from ItemMenu where DescName = 'MyMenu' exists

If yes, i'll Update ItemMenu set BolActive = 1 where IdParent (from the previous select) = Id and check again if IdParent exists here... until IdParent is null/blank and it stops

Is it possible?

P.s. I can't change table structure.

Upvotes: 0

Views: 88

Answers (1)

Cory Buczkowski
Cory Buczkowski

Reputation: 56

Assuming I understand the desired code result, try an update using a recursive common table expression. Provided a screenshot. Apologies for my noob text formatting.

http://s12.postimg.org/cw8yz0isd/Capture.jpg

DECLARE @description AS VARCHAR(50) = 'Hello World'

;WITH CTE AS (

    SELECT  Menu.*
    FROM    dbo.ItemMenu Menu
    WHERE   Menu.descname = @description
    UNION   ALL
    SELECT  Menu2.*
    FROM    dbo.ItemMenu Menu2
    JOIN    CTE
    ON      Menu2.idParent = CTE.Id
)
Update  ActualMenu
SET     BolActive = 1 
FROM    CTE Menu
JOIN    dbo.ItemMenu ActualMenu
ON      ActualMenu.Id = Menu.Id

Upvotes: 2

Related Questions