Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

831 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