PL/SQL Merge source to destination

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

Creating a table of verified customer domains so when a new Contact is added the
email address can be vetted against the list.

Created a temp table to receive the owner_id , owner_type_code and the domain name

CREATE TABLE CCM.CCMTMP4096
( owner_id         NUMBER(15)    NOT NULL
, owner_type_code  NUMBER(6)     NOT NULL
, domain_name      VARCHAR2(100) NOT NULL
)
;

Uploaded attached file Domains_name_uploadV3_180731.xlsx
into CCMTMP4096

The destination table  is created with
CREATE TABLE CCM.CCM_CUSTOMER_DOMAIN
( owner_id         NUMBER(15)    NOT NULL
, owner_type_code  NUMBER(6)     NOT NULL
, domain_name      VARCHAR2(100) NOT NULL
, creation_user    NUMBER(15)    NOT NULL
, creation_date    TIMESTAMP(3)  NOT NULL
, last_update_user NUMBER(15)    NOT NULL
, last_update_date TIMESTAMP(3)  NOT NULL
)
;
No primary key is created because there will be
some Owner_IDs that have multiple DOMAIN_NAMES
and CCM.CCM_CUSTOMER_DOMAIN is a reference
table that will have no children tables or foreign keys.

With these made I tried

merge into CCM.CCM_CUSTOMER_DOMAIN  DOM
using
   (select owner_id, owner_type_code, domain_name
     from CCM.CCMTMP4096) src
     on
     (DOM.domain_name = src.domain_name)
     when matched then update
     set DOM.owner_id = src.owner_id,
     DOM.owner_type_code = src.owner_type_code,
     DOM.domain_name = src.domain_name,
     creation_user = 1,
     creation_date = systimestamp,
     last_update_user = 1,
     last_update_date = systimestamp

But this returned

Error starting at line : 1 in command -
merge into CCM.CCM_CUSTOMER_DOMAIN  DOM
using
   (select owner_id, owner_type_code, domain_name
     from CCM.CCMTMP4096) src
     on
     (DOM.domain_name = src.domain_name)
     when matched then update
     set DOM.owner_id = src.owner_id,
     DOM.owner_type_code = src.owner_type_code,
     DOM.domain_name = src.domain_name,
     creation_user = 1,
     creation_date = systimestamp,
     last_update_user = 1,
     last_update_date = systimestamp
Error at Command Line : 6 Column : 7
Error report -
SQL Error: ORA-38104: Columns referenced in the ON Clause cannot be updated: "DOM"."DOMAIN_NAME"
38104. 00000 -  "Columns referenced in the ON Clause cannot be updated: %s"
*Cause:    LHS of UPDATE SET contains the columns referenced in the ON Clause
*Action:

I suppose it is saying one cannot change in the destination table values that are being
used to compare the source table to the destination table.

So tried
MERGE INTO [dbo].[CCM.CCM_CUSTOMER_DOMAIN] AS [OLD_Tbl]
USING [dbo].[CCM.CCMTMP4096] AS [New_Tbl]
ON ( OLD_Tbl.[domain_name] = New_Tbl.[domain_name] )
WHEN MATCHED AND OLD_Tbl.[domain_name] <> 
New_Tbl.[domain_name]
    UPDATE SET OLD_Tbl.[OWNER_ID] = New_Tbl.[OWNER_ID],
               OLD_Tbl.[owner_type_code] = New_Tbl.[owner_type_code],
               OLD_Tbl.[domain_name] = New_Tbl.[domain_name]
WHEN NOT MATCHED THEN
    INSERT ( [OWNER_ID] ,
             [owner_type_code] ,
             [domain_name]
           )
    VALUES ( New_Tbl.[OWNER_ID] ,
             New_Tbl.[owner_type_code] ,
             New_Tbl.[domain_name]
           )
But the square brackets seem to cause a lot of
 "invalid table name" errors so I tried with a simpler
syntax

MERGE INTO CCM.CCM_CUSTOMER_DOMAIN  OLD_Tbl
USING CCM.CCMTMP4096  New_Tbl
ON (OLD_Tbl.domain_name = New_Tbl.domain_name)
WHEN MATCHED AND OLD_Tbl.domain_name <> 
New_Tbl.domain_name
    UPDATE SET OLD_Tbl.OWNER_ID = New_Tbl.OWNER_ID,
               OLD_Tbl.owner_type_code = New_Tbl.owner_type_code,
               OLD_Tbl.domain_name = New_Tbl.domain_name
WHEN NOT MATCHED THEN
    INSERT ( OWNER_ID ,
             owner_type_code,
             domain_name]
           )
    VALUES ( New_Tbl.OWNER_ID,
             New_Tbl.owner_type_code,
             New_Tbl.domain_name
           )
This gave me "missing keyword"
which leaves me totally lost.

Any ideas on how to get the data in the temp
table can ported into the to destination table.

I guess I could use an old INSERT
statement but it would require 400
of them and I really need to figure out
how the MERGE statement works.

Thanks,

Allen in Dallas
Domains_name_upload_V3_180731.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
The Missing keyword is because of:
WHEN MATCHED AND OLD_Tbl.domain_name <> New_Tbl.domain_name 

I've not seen where you can apply filters on the WHEN MERGED syntax.  That is handled on the "ON" part.  Try just making it "WHEN MATCHED" and see if it works.
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
First I have to ask if that data is safe to upload to a public website?

OK, I have had a chance to actually set up the test case and there are several issues.  The update really doesn't make sense given the only column you can update is the type since the owner and domain identify the unique values.  I added both those to the ON section of MERGE.

You also have several NOT NULL values in the table.  You will need to hard-code those since there isn't any data in the temp table.

Anyway, here is a MERGE that executes.  I cannot say if it is correct or not.  Only you can.  Notice the hard-coded values for the NOT NULL columns.
MERGE INTO CCM_CUSTOMER_DOMAIN  OLD_Tbl
USING CCMTMP4096  New_Tbl
ON (OLD_Tbl.owner_id = New_Tbl.owner_id and OLD_Tbl.domain_name = New_Tbl.domain_name)
WHEN MATCHED THEN
    UPDATE SET OLD_Tbl.owner_type_code = New_Tbl.owner_type_code
WHEN NOT MATCHED THEN
    INSERT ( OWNER_ID ,
             owner_type_code,
             domain_name,
			creation_user,
			creation_date,
			last_update_user,
			last_update_date
           )
    VALUES ( New_Tbl.OWNER_ID,
             New_Tbl.owner_type_code,
             New_Tbl.domain_name,
			12345,
			systimestamp,
			12345,
			systimestamp
           )
/

Open in new window


>>No primary key is created because there will be some Owner_IDs that have multiple DOMAIN_NAMES

Then make the PK owner_id and domain_name?  Nothing says you need a single column primary key.  You can also create a surrogate key with a sequential number.
awking00Information Technology Specialist

Commented:
Where does the creation_user number come from? I can't imagine that it is just arbitrary. By any chance, could it be the user_id from all_users or is there some other table that contains user information?
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!

Allen PittsBusiness analyst

Author

Commented:
Thanks slight for the excellent code with syntax. I got the idea about 'when matched' in the initial reply but struggled with the SQL details.

awking00: You are corrrect. When we are doing input as the the admin user we use the number 1. If the input is by the user the LAST_UPDATE and CREATION_USER is the session USER_ID.

Returns
'405 rows merged'
Sweet.
awking00Information Technology Specialist

Commented:
In that case, when an update is being done by a user the 'WHEN MATCHED' statement should include that information in the update statement-
WHEN MATCHED THEN
    UPDATE SET OLD_Tbl.owner_type_code = New_Tbl.owner_type_code, Tbl.last_update_user = (select user_id from user_users), set Tbl.last_update_date = systimestamp;
Likewise, if an insert is being done by a user, then the WHEN NOT MATCHED statement should include those values-
...
VALUES ( New_Tbl.OWNER_ID,
             New_Tbl.owner_type_code,
             New_Tbl.domain_name,
             (select user_id from user_users),
             systimestamp,
             (select user_id from user_users),
             systimestamp
)
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Instead of the proposed   (select user_id from user_users) above, I would use  sys_context('USERENV','SESSION_USERID')

Then you don't have to perform another query.  I've not tried it in sample code so there is a chance you cannot use the sys_context function in a MERGE like that.

...
VALUES ( New_Tbl.OWNER_ID,
             New_Tbl.owner_type_code,
             New_Tbl.domain_name,
            sys_context('USERENV','SESSION_USERID')
            systimestamp,
             sys_context('USERENV','SESSION_USERID')
            systimestamp
)

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