UPDATE MY_TABLE from TMP_MY_TABLE insert/update mode

[Palmero]

Hi all,

I want to populate MY_TABLE from TMP_MY_TABLE in SQL or PL*SQL in a INSERT/UPDATE mode (update if the record exists and insert if not).
MY_TABLE and TMP_MY_TABLE have the same structure and the same primary key, so strictily the same.
What is the best way to proceed?

Thanks in advance for your answer.

Palmero.

 

[karluk]

The people that write more code than I may have a better suggestion, but I would take the brute force approach: delete all rows from MY_TABLE which have primary keys in TMP_MY_TABLE and then insert all the rows of TMP_MY_TABLE into MY_TABLE. Something like

delete from my_table
where primary_key_col in
(select primary_key_col from tmp_my_table);

insert into my_table
select * from tmp_my_table;

commit;


Naturally there may be practical reasons for avoiding this approach, such as the danger of messing up foreign keys to other tables. However, if your structures allow it, it seems like a clean, uncomplicated solution.

 

[sfvb]

If it was a one time deal, I would just use an insert statement with an exception clause. In the exception, if the exception was a unique constraint violation, then update it.

If it was something I had to use over and over, I would write two cursors, one that caught existing records, and one that caught new records. For example:

CURSOR c_insert IS
   SELECT   *
   FROM     my_tmp_table
   WHERE    (PK(s)) IN
            (   SELECT   PK(s)
                FROM     my_tmp_table
             MINUS
                SELECT   PK(s)
                FROM     my_table
            );
CURSOR c_update IS
   SELECT   *
   FROM     my_tmp_table
   WHERE    (PK(s)) IN
            (   SELECT   PK(s)
                FROM     my_tmp_table
             INTERSECT
                SELECT   PK(s)
                FROM     my_table
            );

Steve

 

[rcurva]

OR you can try...

update my_table
set cols....
where primary_key_col in
(select primary_key_col from tmp_my_table);

IF SQL%NOTFOUND THEN
insert into my_table
select * from tmp_my_table;
END IF;



Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War