Problem with Update query

[abby]

Hi folks,

I am trying to execute a simple update query like :

Update <tablename>
Set <column_name> = <new_value> where
<column_name> = <old_value>

But, I am stuck up when the column to be changed is a part of composite key for that table. Since, the generated query for Update statement will pass only one value to the above mentioned query i.e. for new as well as old column value by replacing the '?' with the input link data.

Currently, my approach to update a composite key column is first to drop the complete row from the table and then re-inserting the deleted row information with the modified composite key column.

At this point, I'm unable to pass two different values to the same column while the Update query is formed by the DataStage.

Also, to ensure that no data is lost while delete and insert operation is done on a particular table I'm looking for an alternate solution.

Could anyone please advise me how to execute an Update statement when composite key columns are involved.

[chulett]

Can you not use Custom / User Defined SQL to accomplish this?

[kumar_s]

Hi,

If i get your issue correctly, cant you update based on two columns,
like
Update <tablename>
Set <column_name> = <new_value> where
<column_name> = <old_value> and <Column_name01> = <old_value_01>

Where the Column_name01 would be one of your key, provided if you can manage to give the value(old_vlaue_01) sequentially.

-Kumar

[jzparad]

The number of columns used in the where clause is simply the number of columns you define as keys in the Database stage. You shouldn't have to use a user-defined query.

[chulett]

You do, as the same column name needs to be mentioned twice in the query.

[jzparad]

Sorry. You're right!

Misread the problem.