UPDATE WHERE PossibleParentItem IN ParentItems

Question

I'm stuck on this query: I would like to UPDATE each row in temp table #tmpHierarchy and set pLevel = 3, if the row has no level set yet (= 99) and one of it's [possible] parent items is contained in the temp table and has level = 2.

Table items contains all items, table ParentItems contains links between Items (fields Item, ParentItem)

Something like:

UPDATE #tmpHierarchy SET pLevel = 3 WHERE pLevel = 99 
      AND (
               (
               SELECT Item FROM Items as IT LEFT JOIN ParentItems as PTS ON PTS.ItemID = IT.ID 
                             WHERE IT.ID = #tmpHierarchy.ItemID
                ) 
           IN
               (
               SELECT Item FROM #tmpHierarchy WHERE pLevel = 2
               )
           )

This would be about what I want to achive, but it returns

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

...for obvious reason, that I have more then one value in IN clause (?).

I would be pleased to get some suggestions to this problem.

Regards,

Oak

Answer 1

OK, so modifing second condition to INNER JOIN seems to give correct results:

              AND EXISTS (
                         SELECT ParItem.ID
                         FROM Items as IT
                         LEFT JOIN ParentItems as PTS ON PTS.ItemID = IT.ID 
                         LEFT JOIN Items as ParItem on ParItem.ID = PTS.ParentItemID
                         INNER JOIN #tmpHierarchy as H ON H.ItemID = ParItem.ID 
                         WHERE IT.ItemID = HI.PartNo AND H.pLevel = 2
                         )

I hope it helps to future viewers.

Regards Oak