DB2 SELECT from UPDATE Options-CodePudding

I am currently trying to do an

SELECT DISTINCT * FROM FINAL TABLE 
(UPDATE mainTable SET value = 'N' WHERE value2 = 'Y')

However, the version of DB2 I have does not appear to support this

SQL Error [42601]: [SQL0199] Keyword UPDATE not expected. Valid tokens: INSERT.

Is there any alternative to this in DB2 that could be return a desired result? Where in one query we can Update and Return the result?

EDIT - The Select statement is supposed to return the values that are to begin processing by a server application. When this happens, a column will be updated to indicate that the Processing of this row has begun. A later command will update the row again when it is completed.

ORIGINAL DATA
ROW ID | COLUMN TWO | PROCESSING FLAG
-------------------------------------------
1      | TASK 1     |      N
2      | TASK 2     |      N
3      | TASK 3     |      N
4      | TASK 4     |      N

After Optimistic Select/Update Query

Data Table returned as:
ROW ID | COLUMN TWO | PROCESSING FLAG
-------------------------------------------
1      | TASK 1     |      Y
2      | TASK 2     |      Y
3      | TASK 3     |      Y
4      | TASK 4     |      Y

This is being called by a .NET Application, so this would be converted into a List of the Table Object.

CodePudding user response：

You can't specify UPDATE in the table-reference in DB2 IBM i 7.3 (and even in 7.4 at the moment) as you could do it in Db2 for LUW.
Only INSERT is available.

data-change-table-reference
- -- FINAL - - TABLE (INSERT statement) correlation-clause
 |          |
- -- NEW ---

CodePudding user response：

While you can't use SELECT FROM FINAL TABLE(UPDATE ...) currently on Db2 for IBM i...

You can within the context of a transaction do
UPDATE mainTable SET value = 'Y' WHERE value2 = 'N' with RR
SELECT * FROM mainTable WHERE value2 = 'Y'
COMMIT

The use of RR - Repeatable read means that the entire table will be locked until you issue your commit. You may be able to use a lower isolation level if you have knowledge/control of any other processes working with the table.

Or if your willing to do some extra work...the below only locks the rows being returned.
UPDATE mainTable SET value = '*' WHERE value2 = 'N' with CHG
SELECT * FROM mainTable WHERE value2 = '*'
UPDATE mainTable SET value = 'Y' WHERE value2 = '*' with CHG
COMMIT

The straight-forward SQL way to do this is via a cursor and an UPDATE WHERE CURRENT OF CURSOR ....

Lastly, since you are using .NET, I suggest taking a look at the iDB2DataAdapter class in the IBM .NET Provider Technical Reference (part of the IBM ACS Windows Application package)

public void Example()
{
    //create table mylib.mytable (col1 char(20), col2 int)
    //insert into mylib.mytable values('original value', 1)

    iDB2Connection cn = new iDB2Connection("DataSource=mySystemi;");
    iDB2DataAdapter da = new iDB2DataAdapter();
    da.SelectCommand = new iDB2Command("select * from mylib.", cn);
    da.UpdateCommand = new iDB2Command("update mylib.mytable set col1 = @col1 where col2 = @col2", cn);

    cn.Open();

    //Let the provider generate the correct parameter information
    da.UpdateCommand.DeriveParameters();

    //Associate each parameter with the column in the table it corresponds to
    da.UpdateCommand.Parameters["@col1"].SourceColumn = "col1";
    da.UpdateCommand.Parameters["@col2"].SourceColumn = "col2";

    //Fill the DataSet from the DataAdapter's SelectCommand
    DataSet ds = new DataSet();
    da.Fill(ds, "table");

    //Modify the information in col1

    DataRow dr = ds.Tables[0].Rows[0];
    dr["col1"] = "new value";

    //Write the information back to the table using the DataAdapter's UpdateCommand
    da.Update(ds, "table");
    cn.Close();
}

You may also find some good information in the Integrating DB2 Universal Database for iSeries with Microsoft ADO .NET Redbook.