Deleting existing record from the oracle if same record number is passed

[Former Member]

Hi all ,

My requirement is as follows.

There is one condition in XI which restricts certain work groups,These work groups will be changing contantly.

Now my requirement is that ,if a record number say 100 exists in my oracle database,when the same record number is passed again,it should delete the old record which is already in the database and insert this new record.In other words all the records which are sent must be inserted with out duplication.

How to achieve this functionality through XI.

let me know.

Thanks,

Srinivasa

[dharamveer_gaur2 · Accepted solution]

Hi

Map the field that u want to update like "100" to Key field.

[dharamveer_gaur2]

Hi

Action should be Update_Insert not Insert_update .

Key Field is use for matching record, suppose if EMP_ID field, u dont want any duplicate insert EMP_ID.

then use this EMP_ID as key field.

As you said pass the record that u want to update as suppose EMP_ID is 100, In target structure map this source field to a field that u want to match in Database.

So if Record EMP_ID 100 already exist then record would be updated else new record inserted to database.

[Former Member]

Hi ,My target structure is

Target->Statement->Attachments-

action=UPDATE_INSERT

table=xxxxx

->access-

Field1

Field2

Field3

Field4

Workgfoup

Now let me noe what are thte modifications required in this structure to achieve my functionality.

Thanks

[Former Member]

will this be ok

Target->Statement->Attachments-

action=UPDATE_INSERT

table=xxxxx

->access-

Field1

Field2

Field3

Field4

Workgroup

key

Workgroup

this structure overwrites th Workgroup.

am i right?

[dharamveer_gaur2]

Hi

JDBC receiver structure use "Update_Insert" Action .

and Send field value that u want to check.

look this help documentation, in this key2 would be check field.

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

[Former Member]

Can you Elobarate Please.

why the key is used .I already used action= Insert update.

Where and how do I compare the old and new records.

Aslo can you Elobarate on JDBC look ups.

Thanks,

Srinivasa

[prateek]

Use BPM/ UPDATE_INSERT along with JDBC lookup.

if a record number say 100 exists in my oracle database

Check this using JDBC lookup.

delete the old record which is already in the database and insert this new record

First create a send step with delete message and then another send message to insert the data. Here you may also use Update_insert

Regards,

Prateek

[dharamveer_gaur2]

use action Update_Insert will update existing record if exist.

[Former Member]

Where to handle this INsert_Update? in CC

How will be the update statement .

update xxx where rcold=rec new?

how do we compare this?

can we do this with ou using BPM?

[prateek]

Where to handle this INsert_Update? in CC

No. In the datatype structure. See here

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

update xxx where rcold=rec new?

You have to use receiver JDBC CC.

can we do this with ou using BPM?

Yes, with UPDATE_INSERT and JDBC lookup.

Regards,

Prateek