update a record on load

[vimotaru]

Hi

I'd like to know if is possible update a record when load a table. The update must be over a record with a primary key.

For example:

I have a record with: id|date_begin|date_end|data
2|02/03/2009|NULL|0

and I want that line to be update when a record like
2|02/03/2009|10/03/2009|56,7
comes.

Is it possible? Primary key is defined by id, date_begin.

What I want is an update instead a reject record.

Thanks in advance.

[darkagn]

Hi vimotaro,

Are you asking if you receive a record you want to update an existing one with the data in that received record? If so, you can do this quite easily by running a SELECT COUNT(*) statement and seeing if this returns 1. If it does, run your UPDATE statement.

If this wasn't what you were asking, please clarify your question for me and I will try to help further :)

[vimotaru]

umm that's the solution as I explained the problem... but I explained it bad. Sorry. ;)

I've doing a periodic load of a file with about 3000 records. The problem is that the new files (each 2 hours I have a new file) have data with the same pk but different info.

I'm wondering if it's possible doing a PL/SQL or any method to update the old records with the new information, avoiding rejected records for unique constraint.

I think the only way is loading the data in a stage table and later load the real table with the processed data, deleting all records before, but I'd like to avoid the staging table and process the staging table twice (one for delete the old data from real table, and one for insert the new data).

Maybe that's the only way to do it, but I wonder if an easier way is possible.

Thanks!!! and sorry for my bad explanation.

[debasisdas]

For that you need to use a staging table and update the master table based upon data in the look up table.

You need to handle the code in a PL/SQL block.