PL/SQL Merge source to destination
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_1807
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_DO
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
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_1807
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_DO
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
ASKER
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.
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.
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
)
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
)
Instead of the proposed (select user_id from user_users) above, I would use sys_context('USERENV','SES
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','SES
systimestamp,
sys_context('USERENV','SE
systimestamp
)
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','SES
systimestamp,
sys_context('USERENV','SE
systimestamp
)
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.