优化这个UPDATE语句不使用游标

问题描述：

FOR v2 AS 
    c2 CURSOR FOR 
     SELECT he.MyPrimary, he.SomeCode, he.SomeName, pe.MyPrimary 
     FROM SomeTable he 
      INNER JOIN AnotherTable pe 
       ON (he.ColOne = pe.FooOne 
        AND he.ColTwo = pe.ColTwo 
        AND he.ColThree = pe.FooOne 
        AND he.SomeCode = pe.SomeCode) 
     WHERE he.relevancy = 1 AND he.ColThree = '2011-01-05' AND he.ColFive = 9 
DO 
    UPDATE AnotherTable SET match = he.MyPrimary, FooTwo = he.SomeCode, SomeName = he.SomeName WHERE MyPrimary = pe.MyPrimary; 

END FOR;

我上面的代码，我试图做到这一点，而无需使用游标，但我不知道该怎么办的UPDATE声明与INNER JOIN。基本上，我想要做的就是连接两个表SomeTable和AnotherTable然后基于一些列的值从SomeTable，值复制到类似列AnotherTable。我正在使用DB2。优化这个UPDATE语句不使用游标

编辑：我只是在寻找到这一点：INNER JOIN in UPDATE sql for DB2

它将使意义做这样的事情，而不是：

UPDATE 
    SomeTable pe 
SET 
    match = (SELECT he.MyPrimary FROM SomeTable he WHERE he.ColOne = pe.FooOne 
          AND he.ColTwo = pe.ColTwo 
          AND he.ColThree = pe.FooOne 
          AND he.SomeCode = pe.SomeCode), 

    FooTwo = (SELECT he.SomeCode FROM SomeTable he WHERE he.ColOne = pe.FooOne 
          AND he.ColTwo = pe.ColTwo 
          AND he.ColThree = pe.FooOne 
          AND he.SomeCode = pe.SomeCode) 
WHERE 
    he.relevancy = 1 AND he.ColThree = '2011-01-05' AND he.ColFive = 9

答

由于你的链接所提到的，ISO/ANSI标准不允许在子查询中使用Update语句之外的联接。因此，你必须要么做多的更新语句，或使每个列的子查询。

Update AnotherTable 
Set match = (
       Select he.MyPrimary 
       From SomeTable he 
       Where he.ColOne = AnotherTable.FooOne 
        And he.ColTwo = AnotherTable.ColTwo 
        And he.ColThree = AnotherTable.FooOne 
        And he.SomeCode = AnotherTable.SomeCode 
        And he.relevancy = 1 
        And he.ColThree = '2011-01-05' 
        And he.ColFive = 9 
       ) 
    , FooTwo = (
       Select he.SomeCode 
       From SomeTable he 
       Where he.ColOne = AnotherTable.FooOne 
        And he.ColTwo = AnotherTable.ColTwo 
        And he.ColThree = AnotherTable.FooOne 
        And he.SomeCode = AnotherTable.SomeCode 
        And he.relevancy = 1 
        And he.ColThree = '2011-01-05' 
        And he.ColFive = 9 
       ) 
    , SomeName = (
        Select he.SomeName 
        From SomeTable he 
        Where he.ColOne = AnotherTable.FooOne 
         And he.ColTwo = AnotherTable.ColTwo 
         And he.ColThree = AnotherTable.FooOne 
         And he.SomeCode = AnotherTable.SomeCode 
         And he.relevancy = 1 
         And he.ColThree = '2011-01-05' 
         And he.ColFive = 9 
        ) 
Where Exists (
       Select 1 
       From SomeTable he 
       Where he.ColOne = AnotherTable.FooOne 
        And he.ColTwo = AnotherTable.ColTwo 
        And he.ColThree = AnotherTable.FooOne 
        And he.SomeCode = AnotherTable.SomeCode 
        And he.relevancy = 1 
        And he.ColThree = '2011-01-05' 
        And he.ColFive = 9 
       )

我真的只是关心性能。这会比使用游标的原始游戏快吗？ – Connection 2011-05-06 22:27:00

@SQLizer - 你将不得不测试它知道。查询引擎可能会明智地意识到你正在试图执行三个相同的子查询。如果不是，另一个解决方案是三个更新查询。这两种解决方案之一应该比光标更快。 – Thomas 2011-05-06 22:51:31

貌似是因为事情的方式设置在这里，光标实际上比其他两种解决方案快（分钟）。所以现在看来，改变它没有意义。 – Connection 2011-05-06 23:59:19

答

有一个更好的方法来做到这一点;

UPDATE SomeTable pe SET (match, FooTwo, SomeName) = (SELECT he.MyPrimary, he.SomeCode, he.SomeName 
                FROM AnotherTable he 
                WHERE he.ColOne = pe.FooOne 
                AND he.ColTwo = pe.ColTwo 
                AND he.ColThree = pe.FooOne 
                AND he.SomeCode = pe.SomeCode) 
WHERE he.relevancy = 1 
AND he.ColThree = '2011-01-05' 
AND he.ColFive = 9

这工作得很好，DB2的iSeries的版本。
如果你需要担心NULL行，不要忘记你的存在子句：

AND EXISTS (SELECT '1' 
      FROM AnotherTable he 
      WHERE he.ColOne = pe.FooOne 
      AND he.ColTwo = pe.ColTwo 
      AND he.ColThree = pr.FooOne 
      AND he.SomeCode = pe.SomeCode)

加入，现有的WHERE条款后，在主UPDATE声明。

优化这个UPDATE语句不使用游标

相关推荐