Page 2 of 2
Posted: Wed Aug 04, 2010 6:52 am
by arunpramanik
Still you can't do ? then my last suggestion is (considering that the table in which you are working is a Fact/detail table) -
Delete the existing rows with old keys and then Insert the new rows with new keys
Please update the Subject to something like "updating key column"
Posted: Mon Aug 16, 2010 3:01 am
by qutesanju
I'm getting an error for this as
SQLSTATE=23000, DBMS.CODE=2601
[DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert duplicate key row in object 'dbo.CREFEE' with unique index 'ix_location_1'.
SQLSTATE=01000, DBMS.CODE=3621
[DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
Posted: Mon Aug 16, 2010 3:12 am
by arunpramanik
Please describe your job design.
Posted: Mon Aug 16, 2010 3:27 am
by qutesanju
job design is
input query -->transformer-->database table
Posted: Mon Aug 16, 2010 3:50 am
by arunpramanik
Considering that we are working with KEY FIELDS.
Here is an alternate design -
Code: Select all
pk1,pk2,pk3 = oldval
input -----------> xfm------------ODBC (delete)
| pk1,pk2,pk3 = newval
|
ODBC (insert)
Use user define statement in each statement for example
DELETE FROM "xyz" WHERE ("pk1" = ? AND "pk2" = ? AND "pk3" = ?);
Posted: Mon Aug 16, 2010 6:19 am
by chulett
qutesanju wrote:I'm getting an error for this as
SQLSTATE=23000, DBMS.CODE=2601
[DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert duplicate key row in object 'dbo.CREFEE' with unique index 'ix_location_1'.
Not really sure what "this" is anymore or how we got to insert errors trying to do an update but the answer here is quite simple -
stop trying to insert duplicate records. If for some reason we're now talking about the need to delete and then insert rather than update, there is usually an Update Action called 'Replace existing row completely', use that. Or simply follow the advice you've been given (more than once) on how to do updates on key fields.
If you still have problems, make sure you let us know what you are actually trying to do and post the SQL that you are using.
Posted: Tue Aug 17, 2010 2:00 am
by HariK
What is the array/transaction size you have set for delete operation?
Posted: Tue Aug 17, 2010 2:46 am
by arunpramanik
I think the Delete operation is not working; and most probable reason can be that you are deleting Master table and FK constraint is not allowing.
But this should not create the INSERT problem, unless the record do exists in Master table with same PKs
Posted: Wed Aug 18, 2010 2:40 am
by arunpramanik
From the very first post -
using a query I can get below recods to update
System Plan Id
P1 102 202BILGN16
P1 102 202BILGN16
P1 102 202BILGN16
P2 103 202BILGN15
P2 103 202BILGN15
P2 103 202BILGN15
Based on this above data at least there is atleast another composite key. Hope you have identified all your keys to work in this type of scenario