Keeping the old value on update

aravindk · Post by **aravindk** » Fri Apr 23, 2004 1:37 pm

Hello all,

In one of my jobs, I am using Insert new or update existing rows. In that I want to move a field from my input stage only while inserting new rows. I want to keep the old value when it is updating an existing row. How can I do that?

Any help is much appreciated.

Regards,

Aravind.

nkumar_home · Post by **nkumar_home** » Fri Apr 23, 2004 1:51 pm

Copy the sql generated by the "Insert new or update Existing rows" option.
Then change to "User defined sql". Paste the sql from above but make the changes to the update part to use the columns from the table instead of the host variables. This same mechanism can be used even if you want to add to an existing numeric column while updating.

aravindk · Post by **aravindk** » Fri Apr 23, 2004 2:09 pm

I am using OCI8 stage and I dont see an option to convert into user-defined sql????

ray.wurlod · Post by **ray.wurlod** » Sun Apr 25, 2004 2:46 am

The option is on the General page of the Inputs tab.

Better design is to segregate the rows to be inserted and updated so that one link can handle "insert new rows only" and the other link can handle "update existing rows only". In this case, preserve the original value in a column simply by not delivering that column to the "update" link.

aravindk · Post by **aravindk** » Sun Apr 25, 2004 6:14 pm

Thanks Ray. After pulling out my hairs for couple of hours, thats exactly what I ended up doing.

Thanks again,