Replace existing rows

sheema · Post by **sheema** » Mon Feb 26, 2007 4:42 pm

I have job which reads data from a abc.txt and loads into oraOCI i am using the update action -Replace existing rows into TableA which has 2 key columns.

But now i need to delete the rows depending on only one key and than insert the rows.so how can do this most efficiently.

Thanks
sheema

kumar_s · Post by **kumar_s** » Mon Feb 26, 2007 4:55 pm

You need to replace based on single column, am I right?
Mark that single column alone as key in Datastage.

sheema · Post by **sheema** » Mon Feb 26, 2007 4:58 pm

If i make single column as key,than if i get 2 rows with same key1 and different col2, i.e

1001,sdfg
1001,wvdf

than would'nt it be a problem with Unique constraint.
Oracle will reject the second row.

kumar_s · Post by **kumar_s** » Mon Feb 26, 2007 5:02 pm

What you expect the output should be for this case?
If there is any record with 1001, it should be replaced with these two records?
And by the way, the database keys has not been changed, only are just marking the key in datastage as one key.
But to be more comfortable, you can do a lookup to the target and replace the rows with the input rows, if not found, insert it.

sheema · Post by **sheema** » Mon Feb 26, 2007 5:08 pm

ok,i want to delete both the 1001 records in the Tbl and insert the new record from the source file.

kumar_s · Post by **kumar_s** » Mon Feb 26, 2007 5:18 pm

Have you tried the option "Replace Existing Row completely" with single filed marked as Key in Datastage.
Observer the SQL generated, a DELETE statement will be issued first with the condition, Where Col1 = :1 (Single field).
Later Insert statement.

sheema · Post by **sheema** » Mon Feb 26, 2007 5:27 pm

yes,that is working fine.I need another clarification
Previously we had 2 jobs

Job1,we read from the source file and than
we used a aggregator to group by on key1 and delete the records from tabl1.

Job2,we read from the same source file and insert the rows into tabl1.

So instead of 2 jobs i am combining them into a single job,by using the update action as 'Replace existing rows completely' and as you suggested by taking a single key column in oraOCI.i just want to make sure that i am right by replacing 2 jobs with a single job.

kumar_s · Post by **kumar_s** » Mon Feb 26, 2007 5:32 pm

I don't get you full business logic on why is it all been done.
Just be aware that, if your input have more that 1 row with the same key value say 1001, you will end up in inserting only the latest one. Where I guess you need to insert both of them.

sheema · Post by **sheema** » Mon Feb 26, 2007 5:36 pm

ok,Kumar .Thanks for you help.

kumar_s · Post by **kumar_s** » Mon Feb 26, 2007 5:38 pm

If you think its resolves your issue, you can mark the topic as resolved.

chulett · Post by **chulett** » Mon Feb 26, 2007 5:40 pm

kumar_s wrote:Just be aware that, if your input have more that 1 row with the same key value say 1001, you will end up in inserting only the latest one. Where I guess you need to insert both of them.

Make sure you understand what Kumar is saying. A second occurance of a key value in your input source will delete the first value that you just inserted!

kumar_s · Post by **kumar_s** » Mon Feb 26, 2007 5:42 pm

I should better learn to phrase sentences clearly.

sheema · Post by **sheema** » Mon Feb 26, 2007 5:49 pm

ok,but when just select key1 as key in oraOCI and run the job
i am getting the warning

ORA-01008: not all variables bound

sheema · Post by **sheema** » Mon Feb 26, 2007 5:55 pm

for example if i have 2 rows

1001,wed
1001,mnb

what i understand is only 2nd row will be inserted into table,if i select 1001 as key and update action as 'Replace existing completely'.Am i right?

chulett · Post by **chulett** » Mon Feb 26, 2007 6:51 pm

Technically, no. The end result is the same, but what actually happens would be this:

1) 1001,wed. Any existing records with 1001 in that field will be deleted then this record will be inserted.
2) 1001,mnb. Any existing records with 1001 in that field (now meaning the "1001,wed" record) will be deleted then this record will be inserted.