CODEWITHSUNDEEP
sqldatabasedb2db2-400
AzarShah
AzarShah

Reputation: 11

DB2 SELECT from UPDATE Options

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.

Upvotes: 1

Views: 3867

Answers (2)

Mark Barinstein
Mark Barinstein

Reputation: 12339

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

A possible emulation is to use a dynamic compound statement, positioned update and a temporary table to save info on updated rows.

--#SET TERMINATOR @
DECLARE GLOBAL TEMPORARY TABLE SESSION.MAINTABLE 
(
  ID INT, COL VARCHAR (10), FLAG CHAR (1) 
) WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED@

INSERT INTO SESSION.MAINTABLE (ID, COL, FLAG)
VALUES
  (1, 'TASK 1', 'N')
, (2, 'TASK 2', 'N')
, (3, 'TASK 3', 'N')
, (4, 'TASK 4', 'Y')
@

DECLARE GLOBAL TEMPORARY TABLE SESSION.UPDRES AS 
(
SELECT ID FROM SESSION.MAINTABLE
) DEFINITION ONLY WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED@


BEGIN
  FOR F1 AS C1 CURSOR FOR
    SELECT ID FROM SESSION.MAINTABLE WHERE FLAG = 'N' FOR UPDATE
  DO
    UPDATE SESSION.MAINTABLE SET FLAG = 'Y' WHERE CURRENT OF C1;
    INSERT INTO SESSION.UPDRES (ID) VALUES (F1.ID);
  END FOR;
END@

SELECT * FROM SESSION.MAINTABLE@
ID COL FLAG
1 TASK 1 Y
2 TASK 2 Y
3 TASK 3 Y
4 TASK 4 Y 
SELECT * FROM SESSION.UPDRES@
ID
1
2
3

Upvotes: 1

Charles
Charles

Reputation: 23823

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.

Upvotes: 0

Related Questions