How to fire an update query using datastage job?
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 63
- Joined: Fri Jun 22, 2007 7:27 am
- Location: Kolkata
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.
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.
-
- Participant
- Posts: 63
- Joined: Fri Jun 22, 2007 7:27 am
- Location: Kolkata
-
- Participant
- Posts: 63
- Joined: Fri Jun 22, 2007 7:27 am
- Location: Kolkata
Considering that we are working with KEY FIELDS.
Here is an alternate design -
Use user define statement in each statement for example
DELETE FROM "xyz" WHERE ("pk1" = ? AND "pk2" = ? AND "pk3" = ?);
Here is an alternate design -
Code: Select all
pk1,pk2,pk3 = oldval
input -----------> xfm------------ODBC (delete)
| pk1,pk2,pk3 = newval
|
ODBC (insert)
DELETE FROM "xyz" WHERE ("pk1" = ? AND "pk2" = ? AND "pk3" = ?);
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.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'.
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.
Last edited by chulett on Tue Aug 17, 2010 6:28 am, edited 1 time in total.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 63
- Joined: Fri Jun 22, 2007 7:27 am
- Location: Kolkata
-
- Participant
- Posts: 63
- Joined: Fri Jun 22, 2007 7:27 am
- Location: Kolkata
From the very first post -
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
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