PL/SQL WHERE NOT EXISTS to do Insert

Allen Pitts
Allen Pitts used Ask the Experts™
on
Hello expert,

An ETL system receives CRM_CASE_ID  from system CRM database and
writes CRM_CASE_ID  into CCM system database in the CRM_CASE table.

Sometimes the system fails to write CRM_CASE_ID  into CCM system database in the CRM_CASE table
Cases get stuck. We find the Cases. A data ad hoc is run to insert the cases.

The data ad hoc selects the  CRM_CASE_ID  and other records from a temp table
and inserts the records into CRM_CASE.

The issue is the CRM_CASE_ID is a primary key in CRM_CASE. Often by the time
the missing CRM_CASE_ID is identified, inserted into the temp table, tested,
sent to the DBAs and acted upon by the DBAs one of the CRM_CASE_ID
manages to get through and the DBA sends me a nasty gram that the
script failed:  
'Received the following errors:
SQL> @INC1793077.sql
INSERT INTO ccm.crm_case(
*
ERROR at line 1:
ORA-00001: unique constraint (CCM.PK_CRM_CASE) violated'

Script copied herewith below.

I was telling one of the developers the problem and he said
a 'where exists' statement could be used to avoid the problem.
After Googling  'where exists'  I think he meant 'WHERE NOT EXISTS'.

Wrote new script and copied herewith below.
Does the syntax look right?

Thanks.

Allen in Dallas

++++++++++++ begin existing script +++++++++++++++++
 INSERT INTO ccm.crm_case(
  CRM_CASE_ID,
  CRM_GUID,
  CRM_FRIENDLY,
  CRM_REQUESTER_ID,
  CRM_REQUESTER_NAME,
  CRM_ACTION_CODE,
  CRM_NAME,
  CRM_REQUEST_DATE,
  CASE_USER_ID,
  USER_DRAFT_ID,
  CCM_ID,
  IS_APPROVED ,
  CREATION_USER,
  CREATION_DATE,
  LAST_UPDATE_USER,
  LAST_UPDATE_DATE,
  CASE_SOLUTION_CODE,
  SOLUTION_DATE,
  CRM_CASE_GUID
)
SELECT
  CRM_CASE_ID,
  CRM_GUID,
  CRM_FRIENDLY,
  CRM_REQUESTER_ID,
  CRM_REQUESTER_NAME,
  CRM_ACTION_CODE,
  CRM_NAME,
  CRM_REQUEST_DATE,
  CASE_USER_ID,
  USER_DRAFT_ID,
  CCM_ID,
  IS_APPROVED ,
  CREATION_USER,
  CREATION_DATE,
  LAST_UPDATE_USER,
  LAST_UPDATE_DATE,
  CASE_SOLUTION_CODE,
  SOLUTION_DATE,
  CRM_CASE_GUID
FROM DEVUSER.TMP_CASE_INSRT_FNL
;

COMMIT;
+++++++++++++ end exiting script++++++++++++++++

============  begin new script ================
 INSERT INTO ccm.crm_case(
  CRM_CASE_ID,
  CRM_GUID,
  CRM_FRIENDLY,
  CRM_REQUESTER_ID,
  CRM_REQUESTER_NAME,
  CRM_ACTION_CODE,
  CRM_NAME,
  CRM_REQUEST_DATE,
  CASE_USER_ID,
  USER_DRAFT_ID,
  CCM_ID,
  IS_APPROVED ,
  CREATION_USER,
  CREATION_DATE,
  LAST_UPDATE_USER,
  LAST_UPDATE_DATE,
  CASE_SOLUTION_CODE,
  SOLUTION_DATE,
  CRM_CASE_GUID
)
SELECT
  CRM_CASE_ID,
  CRM_GUID,
  CRM_FRIENDLY,
  CRM_REQUESTER_ID,
  CRM_REQUESTER_NAME,
  CRM_ACTION_CODE,
  CRM_NAME,
  CRM_REQUEST_DATE,
  CASE_USER_ID,
  USER_DRAFT_ID,
  CCM_ID,
  IS_APPROVED ,
  CREATION_USER,
  CREATION_DATE,
  LAST_UPDATE_USER,
  LAST_UPDATE_DATE,
  CASE_SOLUTION_CODE,
  SOLUTION_DATE,
  CRM_CASE_GUID
FROM DEVUSER.TMP_CASE_INSRT_FNL
WHERE NOT EXISTS 
(SELECT 
  CRM_CASE_ID,
  CRM_GUID,
  CRM_FRIENDLY,
  CRM_REQUESTER_ID,
  CRM_REQUESTER_NAME,
  CRM_ACTION_CODE,
  CRM_NAME,
  CRM_REQUEST_DATE,
  CASE_USER_ID,
  USER_DRAFT_ID,
  CCM_ID,
  IS_APPROVED ,
  CREATION_USER,
  CREATION_DATE,
  LAST_UPDATE_USER,
  LAST_UPDATE_DATE,
  CASE_SOLUTION_CODE,
  SOLUTION_DATE,
  CRM_CASE_GUID from ccm.crm_case
        WHERE ccm.crm_case.crm_case_id = DEVUSER.TMP_CASE_INSRT_FNL.crm_case_id)
;

COMMIT;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Philippe DamervalSenior Analyst Programmer

Commented:
Personally, I would simply do a left join between TMP_CASE_INSRT_FNL and crm_case on crm_case_id and insert from the rows where crm_case.crm_case_ID IS NULL. Seems cleaner than a WHEN (NOT) EXISTS which doesn't work in all INFORMIX versions.
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Any reason you don't use a MERGE statement similar to your previous questions:
https://www.experts-exchange.com/questions/29111589/PL-SQL-Merge-source-to-destination.html
https://www.experts-exchange.com/questions/28956373/PL-SQL-Update-Location-Names.html
etc.

As far as if your new method works, set up a quick test and try it.  If you don't have a play database to develop on, use Oracle Live SQL:
https://livesql.oracle.com/apex/f?p=590:1000

You can set up a couple test tables, insert whatever you want and see how your proposed solution works.

Most of the time that is what we do before we post.  We set up test cases and make sure things run.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>which doesn't work in all INFORMIX versions.

Informix?  Where was that mentioned?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

lcohanDatabase Analyst

Commented:
That looks right however in the <<WHERE NOT EXISTS...>> you don't need to SELECT all columns but the KEY column that's causing the constraint violation - in your case I believe would be like:

INSERT INTO ccm.crm_case(
  CRM_CASE_ID,
  CRM_GUID,
  CRM_FRIENDLY,
  CRM_REQUESTER_ID,
  CRM_REQUESTER_NAME,
  CRM_ACTION_CODE,
  CRM_NAME,
  CRM_REQUEST_DATE,
  CASE_USER_ID,
  USER_DRAFT_ID,
  CCM_ID,
  IS_APPROVED ,
  CREATION_USER,
  CREATION_DATE,
  LAST_UPDATE_USER,
  LAST_UPDATE_DATE,
  CASE_SOLUTION_CODE,
  SOLUTION_DATE,
  CRM_CASE_GUID
)
SELECT
  CRM_CASE_ID,
  CRM_GUID,
  CRM_FRIENDLY,
  CRM_REQUESTER_ID,
  CRM_REQUESTER_NAME,
  CRM_ACTION_CODE,
  CRM_NAME,
  CRM_REQUEST_DATE,
  CASE_USER_ID,
  USER_DRAFT_ID,
  CCM_ID,
  IS_APPROVED ,
  CREATION_USER,
  CREATION_DATE,
  LAST_UPDATE_USER,
  LAST_UPDATE_DATE,
  CASE_SOLUTION_CODE,
  SOLUTION_DATE,
  CRM_CASE_GUID
FROM DEVUSER.TMP_CASE_INSRT_FNL
WHERE NOT EXISTS 
(SELECT  CRM_CASE_ID 
        FROM ccm.crm_case
        WHERE ccm.crm_case.crm_case_id = DEVUSER.TMP_CASE_INSRT_FNL.crm_case_id);

Open in new window


There's another option as well and you could look at MERGE instead of the NOT EXISTS as it may be better optimized for large record sets and you could skip the UPDATE if not needed - just use the MATCHED...INSERT as per this article:

https://oracle-base.com/articles/9i/merge-statement
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
purely as a note for future reference

WHERE | NOT | EXISTS ( ... )

does NOT need to return any data, it only needs to return true or false. The core of this approach is in the FROM & WHERE clauses of the subquery which tests if any row matches to data to the outer table(s). If there is any match it returns true, otherwise false.

In effect the select clause of the subquery is just there for decoration (and to comply with syntax rules :), so if using EXISTS don't over complicate the select clause of the subquery,
Either of these will work:

WHERE NOT EXISTS (
      SELECT  NULL
     FROM ccm.crm_case   /* important part is here */
      WHERE ccm.crm_case.crm_case_id = DEVUSER.TMP_CASE_INSRT_FNL.crm_case_id

      )

or

WHERE NOT EXISTS (
      SELECT  1
     FROM ccm.crm_case   /* important part is here */
      WHERE ccm.crm_case.crm_case_id = DEVUSER.TMP_CASE_INSRT_FNL.crm_case_id

      )
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I prefer creativity.  I mean how often to you get the opportunity?
...
WHERE NOT EXISTS (
      SELECT  'I like cupcakes'
...
Allen PittsBusiness analyst

Author

Commented:
Thanks for the tip on the Oracle sandbox. I agree that hands on is the best way to figure things out and learn. Unfortunately I am in a fist fight with the DBAs over permissions in the dev environment so an online dev is helpful.
Also I thought I could use the primary key in the WHERE NOT EXISTS subquery and not list all the fields.
Finally, It is seen that merge would get the job done. Did not think of it. Will try it next time.
Thanks
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
thought I could use the primary key in the WHERE NOT EXISTS subquery

You can, but use it in the where clause. Not the select clause.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial