UPDATE WHERE PossibleParentItem IN ParentItems

Question

我被困在这个查询:我想更新每一行在临时表#tmpHierarchy和设置pLevel = 3，如果行还没有水平集(= 99)，它的一个[可能的]父项目包含在临时表和有水平= 2。

表项包含所有项，表ParentItems包含项之间的链接(字段Item, ParentItem)

类似:

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
               )
           )

这将是我想要实现的，但它返回

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.

…很明显，我在in子句中有多个值(?)

我很高兴能得到一些关于这个问题的建议。

问候,橡树

Answer 1

我很确定问题是查询的那一部分:

SELECT Item FROM Items as IT LEFT JOIN ParentItems as PTS ON PTS.ItemID = IT.ID 
                         WHERE IT.ID = #tmpHierarchy.ItemID

它返回了不止一行，并且你得到了多对多的比较，这是IN子句不允许的。

你可以这样修改查询:

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

Answer 2

如果使用IN，则左子查询必须是"单行"结果，请尝试:

UPDATE #tmpHierarchy SET pLevel = 3 WHERE pLevel = 99 
      AND (
            SELECT Item 
            FROM ParentItems as PTS WHERE PTS.ItemID = #tmpHierarchy.ItemID AND
                   #tmpHierarchy.pLevel = 2
           ) IS NOT NULL

我假设条目属于ParentItems。

这是我在这个网站上的第一个评论，任何提示或投诉都可以随意做。

编辑:现在我明白了，因为SELECT…水平= 99…(# tmpHierarchy。级别= 2是不可能的)，由在EXISTS中具有正确连接的所有者解决。

Answer 3

OK，那么将第二个条件修改为INNER JOIN似乎会得到正确的结果:

              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
                         )

我希望它对未来的观众有所帮助。

把橡木