Optimize this UPDATE statement to not use cursors

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.Som开发者_C百科eCode, SomeName = he.SomeName WHERE MyPrimary = pe.MyPrimary;  

END FOR;

I have the above code and I'm trying to do this without using cursors but I'm not sure how to do an UPDATE statement with an INNER JOIN. Basically, what I'd like to do is join two tables SomeTable and AnotherTable then based on some column values from SomeTable, copy the value to a similar column in AnotherTable. I'm using DB2.

EDIT: I was just looking into this: INNER JOIN in UPDATE sql for DB2

Would it make sense to do something like this instead:

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

---

As your link mentioned, the ISO/ANSI standard does not allow for a join in an Update statement outside of its use in a subquery. Thus, you have to either do multiple Update statements, or so a subquery for each column.

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
                )

---

There's a slightly better way to do this;

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

This works pretty well on the iSeries version of DB2.
If you need to worry about NULL rows, don't forget your exists clause:

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)  

Add that after the existing WHERE clause in the main UPDATE statement.