Defining an UPDATE Statement 

Use

You use an UPDATE statement to change existing table values. The statement corresponds to an SQL UPDATE statement.

Format of UPDATE Statement

<StatementName>

<dbTableName action=”UPDATE”>

<table>realDbTableName</table>

<access>

<col1>val1</col1>

<col2>val2new</col2>

</access>

<key1>

<col2>val2old</col2>

<col4>val4</col4>

</key1>

<key2>

<col2>val2old2</col2>

</key2>

</dbTableName>

</StatementName>

Procedure

...

       1.      Enter the new column values in the <access> element.

Enter exactly one <access> element.

       2.      In the <key> element, enter the condition that can find the data records whose column values are to be changed.

○       You can use any number of <key> elements to formulate your condition.

○       Column values within a <key> element are combined with a logical AND; different <key> elements are combined with a logical OR.

○       If you do not define the <key>element, or if you define an empty <key> element, this means that no condition is specified and that the entire table is to be changed.

If you want to ensure this does not happen, select Key Tags Mandatory in the adapter configuration.

○       If you have not formulated a condition in the <key> elements, but have selected Key Tags Mandatory, this results in an error in message processing with a corresponding error output.

Result

The corresponding SQL statement in the XML structure above is as follows:

“

UPDATE dbTableName  SET col1=’val1’, col2=’val2new’ WHERE ((col2=’val2old’ AND col4=’val4’) OR (col2=’val2old2’))

“

The column type STRING is used for all columns. The character “ may be missing in other column types.

The response document contains the following element as well as the number of updated table rows, including 0.

<update_count>count</update_count>