Update

[Former Member]

I need to Update a Database Table.(If the Row doesnot exists then I need to Insert that row)

My Table name : Emp

***************************************

Table Structure : EmpNo

-

---

Empname

****************************************

Key : Empno

***************************************

I have designed my Data Structure like below in XI

Employee_DT

-- Statement1

-

---

action

-

---

table

-

---

access

-

---

EmpNo

-

---

EmpName

-

---

Key

================================================

Mapping

Update_Insert -

---

> action

emp----

---

> table

Empty Constant----

---

> Access

--- Employee Number -

---

> empno

--- Employee Name -

---

> empname

My question is what is the purpose of KEY and what should I mention there?

Thanks for your Help

[Former Member · Accepted solution]

Hi Navven,

Key is the key field of your database table, such as employee number. You need to pass this value to update a specific record in the database table. say, you want to update specific employee record then you need to pass particular employee number to this field.

Check the SAP documentation for more info...

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

Regards

Anand

Message was edited by: Anand Torgal

[Former Member · Accepted solution]

Thanks Anand.

[Former Member · Accepted solution]

Apart from using as a primary key , you can use this as a where(condition) clause to update a specific record in the database table. Check SAP help for more information..

action=UPDATE

Statements with this action cause existing table values to be updated. Therefore, the statement corresponds to an SQL UPDATE statement.

The <access> block contains the new column values and a <key> element contains the columns whose values must be identical with the specified value to get the new column values. The name of the <key> element is arbitrary. Column values within a <key> element are combined with a logical AND; different <key>elements are combined with a logical OR.

A statement with the action UPDATE must have exactly one <access>element. The number of <key> elements with arbitrary names is not restricted.

The corresponding SQL statement for StatementName1 in the example above is as follows:

“

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

“

As in the other examples, 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 lines, including 0.

<update_count>count</update_count>

If there is no <key> element, or if there is a <key> element but it is empty, then no condition is specified and the entire table is to be updated. This may not be permitted by the configuration of the JDBC adapter for security reasons and will therefore result in an error during message processing and an appropriate error message.

Your message should be of this format to update a specific record..

<root>

<StatementName1>

<dbTableName action=”UPDATE” | “UPDATE_INSERT”>

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

</StatementName1>

Regards

Anand

Message was edited by: Anand Torgal