Solved

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

Posted on 2011-02-24
4
590 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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 80 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 100 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 70 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

932 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now