?
Solved

What is the best way to do an INSERT/UPDATE with best practice?

Posted on 2011-02-24
4
Medium Priority
?
597 Views
Last Modified: 2013-12-07
Hi,
What is the best way to do an INSERT/UPDATE with best practice?
I'm using the below code...

These are selected items from a multi select where I want to insert new ones, activate (status_id = 1) the existing that are deactive and disable ONLY the ones that are active and are not selected.

deactivating all as below hits other code it should not because it is not really a deactivate

Thank you!
-- First disable all existion
	Update OM_DOC_ORG_TYPE_XREF 
	set STATUS_ID = 2
	    where DOC_TEMPL_ID = :P320_DOC_TEMPL_ID;

FOR j IN 1..l_selected.count LOOP
  BEGIN
-- Then insert new ones activated
     insert into OM_DOC_ORG_TYPE_XREF(DOC_TEMPL_ID,ORG_TYPE_ID, STATUS_ID) 
     values (:P320_DOC_TEMPL_ID, l_selected (j), 1);

  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
-- reactivate existing ones
	Update OM_DOC_ORG_TYPE_XREF 
	set ORG_TYPE_ID = l_selected (j),
	    STATUS_ID = 1
	    where DOC_TEMPL_ID = :P320_DOC_TEMPL_ID;

  END;      
END LOOP;

Open in new window

0
Comment
Question by:bcarlis
4 Comments
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 320 total points
ID: 34973575
What version of Oracle?

Looks like you are wanting to do an upsert,  take a look at MERGE.

http://psoug.org/reference/merge.html
0
 
LVL 3

Accepted Solution

by:
LFLFM earned 400 total points
ID: 34973947
Well... unless you are dealing with 1000's of lines, it would probably be best to use a MERGE (depending on Oracle version) or an insert-select and an update statement

example:
insert into OM_DOC_ORG_TYPE_XREF OMT
select DOC_TEMPL_ID,ORG_TYPE_ID, STATUS_ID
from <tables>
where <condition>
and omt.DOC_TEMPL_ID = :P320_DOC_TEMPL_ID
and not exists (select 1 from OM_DOC_ORG_TYPE_XREF SOMT
            where SOMT.DOC_TEMPL_ID = OMT.DOC_TEMPL_ID
            and SOMT.ORG_TYPE_ID = <org_type_from_select>)
/
update OM_DOC_ORG_TYPE_XREF OMT
set STATUS_ID = 1
where omt.DOC_TEMPL_ID = :P320_DOC_TEMPL_ID
and exists (select 1from <tables>
                where <condition>
                and SOMT.DOC_TEMPL_ID = OMT.DOC_TEMPL_ID
                and SOMT.ORG_TYPE_ID = <org_type_from_select>)

Open in new window


If you are dealing with 1000's of lines, then your method seems descent enough... :-)
0
 
LVL 13

Assisted Solution

by:riazpk
riazpk earned 280 total points
ID: 35079668
As suggested by other, benchmark MERGE and other options provided.

About your current approach:

=> Put INSERT first if probability that the row will be inserted successfully is more (as compared to probability that a similar row already exists).

   INSERT
Exception
   WHEN DUP_VAL_ON_INDEX THEN
     UPDATE;
END;

=> Put UPDATE first otherwise.

UPDATE
IF SQL%NOTFOUND THEN
  INSERT;
END IF;
0
 
LVL 2

Author Closing Comment

by:bcarlis
ID: 35098361
Thank you!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question