Updating A Column Base on Another Column

I'm wondering if it's possible to do something like the following:

UPDATE SomeTable st
    SET MyColumn1 = (SELECT SomeValue
                     FROM WhereverTable),

        MyColumn2 = ((SELECT AnotherValue
                     FROM AnotherTable)
                     *
                     MyColumn1);
WHERE MyColumn4 = 'condition'

I'm thinking that when I multiply AnotherValue with MyColumn1, it will still have the old value of MyColumn1 rather than the new one which is supposed to be 开发者_开发技巧SomeValue.

I'm using DB2 if that matters.

---

Count on the multiplication expression using the original value of MyColumn1, not the value specified in the update. If you want to use the new value for MyColumn1 in the multiplication formula, then specify the new expression there, too. Also, you should place a MIN, MAX, or FETCH FIRST ROW ONLY in the subqueries to prevent multiple rows from being returned.

---

Without more specifics than that, it is hard to give you a solid answer. But I'll take a stab at it:

    UPDATE SomeTable
       SET MyColumn1 = wt.SomeValue
           MyColumn2 = at.AnotherValue
      FROM SomeTable st
CROSS JOIN (
               SELECT SomeValue FROM WhereverTable
           ) wt
CROSS JOIN (
               SELECT AnotherValue FROM AnotherTable
           ) at
WHERE MyColumn4 = 'condition'

If they truly aren't related, then CROSS JOIN Is what you want. But beware that the subqueries (in this case, wt and at) that are cross-joined need to have only 1 record in them, or the JOINs will cause more than one record to be generated in the FROM clause. Not sure what it would do to this query, but it would probably make the resultset non-updateable.

Note that I'm using SQL Server's T-SQL syntax, as that is what I'm more familiar with. But a quick google found that DB2 does support cross joins (see here).

---

Try this (untested):

UPDATE SomeTable 
    SET MyColumn1 = (SELECT SomeValue
                     FROM WhereverTable),

        MyColumn2 = MyColumn1 * (SELECT AnotherValue
                                 FROM AnotherTable)
WHERE MyColumn4 = 'condition';

This Standard SQL-92 syntax requires scalar subqueries i.e. both WhereverTable and AnotherTable must each consist of zero or one row. It is more often the case that that rows need to be 'correlated' using identifiers (or conditions or similar) in the subqueries and do so in both the SET clause and the WHERE clause in the UPDATE statement e.g. (SQL-92, untested):

UPDATE SomeTable
   SET MyColumn1 = (
                    SELECT wt.SomeValue
                      FROM WhereverTable AS wt
                     WHERE wt.some_table_ID = SomeTable.some_table_ID
                   ),
       MyColumn2 = MyColumn1 * (
                                SELECT av.AnotherValue
                                  FROM AnotherTable AS av
                                 WHERE wt.another_table_ID = SomeTable.another_table_ID
                               )
 WHERE MyColumn4 = 'condition'
       AND EXISTS (
                   SELECT *
                     FROM WhereverTable AS wt
                    WHERE wt.some_table_ID = SomeTable.some_table_ID
                  )
        OR EXISTS (
                   SELECT *
                     FROM AnotherTable AS av
                    WHERE wt.another_table_ID = SomeTable.another_table_ID
                  ); 

This can be rewritten using SQL-99's MERGE statement given less 'repetitive' code.

---

Assuming that the tables always return one (and only one) row, the following should work just fine:

UPDATE SomeTable st SET (MyColumn1, MyColumn2) = (SELECT SomeValue, 
                                                         AnotherValue * MyColumn1
                                                  FROM WhereverTable wt
                                                  CROSS JOIN AnotherTable at)
WHERE MyColumn4 = 'condition'

This will update MyColumn2 as desired (using the old value of MyColumn1).
Obviously if there are more/optional rows things get more complicated.