User Defined Update SQL in DB2 UDB API

[arnabbora]

Hi Everyone,

I am trying to update a DB2 table using user defined SQL. The reason I am using an user defined SQL is that , I need to Update a Field to a New value from its Old value, and in the "Where clause" i need the Old value to pick the proper row.

Here are the fields from the table that I am using:

VALUE_KEY, SERVICE_ID, DIM_KEY, CLIENT_ID, WEEK_KEY.

I need to update VALUE_KEY and SERVICE_ID to new values depending on the existing values of DIM_KEY, CLIENT_ID, WEEK_KEY and VALUE_KEY

So the update sql will be something like ,

UPDATE ATTRIBUTE_TABLE SET VALUE_KEY=?,SERVICE_ID=?
WHERE DIM_KEY=? and CLIENT_ID=? and WEEK_KEY=? and VALUE_KEY=OLD_VALUE_KEY.

I have the OLD_VALUE_KEY(the Old value ,which I need to update to New value) field in the columns list.

Now how can I pass this Old value to the "Where" clause of the query in the DB2 API stage?

When I set the user defined SQL as

UPDATE ATTRIBUTE_TABLE SET VALUE_KEY=VALUE_KEY,SERVICE_ID=SERVICE_ID
WHERE DIM_KEY=DIM_KEY and CLIENT_ID=CLIENT_ID and WEEK_KEY=WEEK_KEY and VALUE_KEY=OLD_VALUE_KEY

it is not updating the value. Can we use the fields like VALUE_KEY=VALUE_KEY in User defined SQL Or we need to handle this in another way?

If someone can help me on this I will be really thankful.

Thanks,
Arnie

[chulett]

UPDATE ATTRIBUTE_TABLE SET VALUE_KEY=?,SERVICE_ID=?
WHERE DIM_KEY=? and CLIENT_ID=? and WEEK_KEY=? and VALUE_KEY=?

Where you bind the OLD_VALUE_KEY column into the proper place in the SQL just like every other field.

[arnabbora]

Hi Craig,
Thanks for quick reply.
But, I am not able to see your complete post as the privilege is for only premium members
Could you please make your post public for some time?

Thanks,
Arnie

[chulett]

Didn't notice this was your first post here and you can't be missing all that much of what I posted, so sure.

[arnabbora]

Hi Craig,
Thank you very much.
It worked..


Regards,
Arnie