User defined SQL not working for update

[senthilt1]

Hi All,

I am trying to update a table using the userdefined query...

UPDATE Table SET col4= ? WHERE (col2=? And Col3=?)
Message: 0 rows affected...
The columns in the where clause are not the key column in database...but in datastage i enabled it as key..even in the where clause if i add primary key along with that...it is not getting updated.

But the update query works if i give the query in this way..

Update table set col1=?, col2=? col3=? and col4=? where (col1=?)

col1 - is the actual primary key of the table in database.

But i dont want to update col1, col2, col3...i really want to update only col4 based on col2 and col3...

Why am getting this problem..please let me know.

Thanks,

Senthil P

**Note: Subject changed to be more descriptive - Content Editor **

[swarnkar]

Senthil,

It seems that there is no matching rows in source data with target table when you are using key as (col2=? And Col3=?). If that is not the case then define Col2 and Col3 as key column and remove Col1 from target side and then use generated sql. I think it will work.

Regards,
Nitin Swarnkar.

[mk_ds09]

Key columns in Ds should be checked when it is the part of primay Key..
Can you uncheck those in DS and test your job again..?

[chulett]

Key columns in Ds should be checked when it is the part of primay Key..

This is incorrect. Key columns in DataStage have nothing to do with "keys" (primary or otherwise) or indexes in your database. For update or delete DML they simply indicate the columns to be included in the 'where' clause. Sure, it can help performance if they are indexed but that's got nothing to do with how things work, sql-wise.

ps. If you want to update two columns based on one other column, then only send three columns to the stage. While the ODBC stage will support sending unused columns, it is not a good practice and the native db stages will not support that.