[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2011-02-24
4
Medium Priority
?
596 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 77

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.

649 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