Solved

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

Posted on 2011-02-24
4
589 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Help joining two tables 7 33
Help with SQL Query 23 39
dates - loop 12 39
sql query 9 18
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…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

772 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

11 Experts available now in Live!

Get 1:1 Help Now