Does Db2 Have an "Insert or Update" Statement

Db2: How to insert new and update existing data?

I found a solution that does what I want but I don't know if it's the best way or not ! Anyway I post it to help who's facing the same problem but if anyone's have another suggestion it's welcome :

MERGE INTO KDDBD01.KDTCTCT AS CT USING (SELECT COUNT(ADR_MAIL_UTIL) FROM KDDBD01.KDTCTCT) ON (CT.ADR_MAIL_UTIL = :contactEmail) WHEN NOT MATCHED THEN INSERT (CT.ADR_MAIL_UTIL) VALUES (:contactEmail)

DB2 SQL Statement for Inserting new data and updating existing data

I'm a bit puzzled by your query. Reading the text makes me suspect that you want something like this:

merge into table1 as t1
using ( values (123, 'abc') ) as t2 (id, ref)
    on t1.id = t2.id
when matched then update 
    set t1.ref = t2.ref
when not matched then 
    insert (id, ref) values (t2.id, t2.ref);

Is that correct?

DB2 SELECT from UPDATE Options

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.

How to insert updated rows in a new table on DB2 - IBM

You have to use SELECT as an outermost statement and keep inner SELECTs in distinct CTEs, if you have a number of them.

Try this:

WITH A AS
(
  SELECT ID, NAME
  FROM FINAL TABLE
  (
    UPDATE GDPR
    SET NAME = 'Some name'
    WHERE ID = 1
  )
)
SELECT COUNT (1)
FROM FINAL TABLE
(
  INSERT INTO GDPR_LOG (ID, NAME)
  SELECT * FROM A
) B

Update:

Using dynamic SQL.

You must enclose the whole statement with some statement termination character (say, @) different from the default one (;) if you use some tool to run this compound statement and specify this statement terminator correctly there.

BEGIN
  DECLARE C1 CURSOR FOR S1;
  
  PREPARE S1 FROM  
'
WITH A AS
(
  SELECT ID, NAME
  FROM FINAL TABLE
  (
    UPDATE GDPR
    SET NAME = ?
    WHERE ID = ?
  )
)
SELECT COUNT (1)
FROM FINAL TABLE
(
  INSERT INTO GDPR_LOG (ID, NAME)
  SELECT * FROM A
) B
';
  
  OPEN C1 USING 'Name', 1;
  CLOSE C1;
    
END

---

Related Topics