Re: How to REPLACE a unique field?

Hello,

"Ramon F Herrera" <ramon_at_conexus.net> wrote in message news:c9bc36ff.0403091531.69bfdcc4_at_posting.google.com...
> (newbie and RTFM question, already tried Google, and wouldn't
> know in what manual page to begin searching, so bear with me)
>
>
> I have a table with a unique field (employee ID).
> Currently, every time a given employee ID is already
> inserted, the next repeated one is rejected and I get
> a "constraint violated" in the log file. What I'd like
> to do is load the data in chronological order, and every
> time a repeteated ID comes along, it should _overwrite_
> the previous one. That way, I will end up most the most
> up-to-date record.
>

If you are running 9i and updating table t1 from a staging table t2, you can do this kind of stuff with the MERGE statement :

merge into t1

    using t2
    on ( t2.employeeid = t1.employeeid )     when matched then

       update set t1.col1 = t2.col1, ...etc.     when not matched then

        insert (employeeid, col1, ...) values( t2.employeeid, t2.col1,....);

If what you are doing is inserting individual rows from a client (Java, etc), then

1. you can start with an update: int rows = stmt.executeUpdate(...); // Java ... and if rows == 0, then execute insert.
2. start with an insert and if an execption occcurs (unique constraint violated), then do an insert.

The choice between (a) and (b) depends on the probability of success of either.

VC

>. Herrera
Received on Tue Mar 09 2004 - 18:13:31 CST