• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 124
  • Last Modified:

I am trying to merge records (i.e update records) if the incoming data is not matching the master table. If NULL dont overwrite

Please see this query below using with I am trying to update records in master table (DATACOLLECTION) with records in APEX.DATACOLLECTION_TEMP table

Statement 1:

MERGE INTO apex.DATACOLLECTION tgt
USING ( select * from APEX.DATACOLLECTION_TEMP
    minus
 select * from APEX.DATACOLLECTION) src
 on (tgt.SERVERNAME = src.SERVERNAME)
 when matched then
 update set tgt.RETIREDDATE = src.RETIREDDATE,
tgt.APPLICATION_DESCRIPTION = src.APPLICATION_DESCRIPTION,
tgt.APP_LEVEL = src.APP_LEVEL,
tgt.PRPC_VERSION = src.PRPC_VERSION,
tgt.TEST_GROUP = src.TEST_GROUP,
tgt.APP_ENGINE = src.APP_ENGINE,
tgt.APP_JAVA_VERSION = src.APP_JAVA_VERSION,
tgt.APP_OWNER = src.APP_OWNER,
tgt.APP_PRIMARY_URL = src.APP_PRIMARY_URL,
tgt.APP_SECURE_URL = src.APP_SECURE_URL,
tgt.APP_F5_URL = src.APP_F5_URL,
tgt.APP_TOMCATMANAGER = src.APP_TOMCATMANAGER,
tgt.FUNCTION = src.FUNCTION,
tgt.DBSERVER = src.DBSERVER,
tgt.DBNAME = src.DBNAME,
tgt.DBVERSION = src.DBVERSION,
tgt.OS = src.OS,
tgt.CREATE_DATE = src.CREATE_DATE,
tgt.LOCATION = src.LOCATION,
tgt.LICENSE = src.LICENSE,
tgt.CPU = src.CPU,
tgt.RAM = src.RAM,
tgt.STORAGE = src.STORAGE,
tgt.JVM_INITIAL = src.JVM_INITIAL,
tgt.JVM_MAX = src.JVM_MAX,
tgt.SSL_CERT_EXP_DATE = src.SSL_CERT_EXP_DATE,
tgt.SINGLESIGNON = src.SINGLESIGNON,
tgt.URL_OBFUSCATION = src.URL_OBFUSCATION,
tgt.IP_ADDRESS = src.IP_ADDRESS,
tgt.SMA_LOCKDOWN = src.SMA_LOCKDOWN

If the data in TEMP table is NULL then I dont want to overwrite the NULL with the existing value in datacollection table.

Please let me know a query which I can use so that I dont have to overwrite with  NULL

something like:

MERGE INTO apex.DATACOLLECTION tgt
USING ( select * from APEX.DATACOLLECTION_TEMP
    minus
 select * from APEX.DATACOLLECTION) src
 on (tgt.SERVERNAME = src.SERVERNAME)
 when matched then
 update set tgt.RETIREDDATE = src.RETIREDDATE WHEN src.RETIREDDATE IS NOT NULL


Please give me a query for Statement 1
0
DevSupport
Asked:
DevSupport
  • 11
  • 9
  • 4
  • +1
3 Solutions
 
PatHartmanCommented:
In Access SQL, I would use the NZ() function (null to 0)

MERGE INTO apex.DATACOLLECTION tgt
USING ( select * from APEX.DATACOLLECTION_TEMP
    minus
 select * from APEX.DATACOLLECTION) src
 on (tgt.SERVERNAME = src.SERVERNAME)
 when matched then
 update set tgt.RETIREDDATE = Nz(src.RETIREDDATE,tgt.RETIREDATE),
tgt.APPLICATION_DESCRIPTION = Nz(src.APPLICATION_DESCRIPTION, tgt.APPLICATION_DESCRIPTION),
tgt.APP_LEVEL = Nz(src.APP_LEVEL,tgt.APP_LEVEL),
....

T-SQL has something similar but I'm not sure about Oracle.
0
 
awking00Commented:
Use the nvl2 function for the value to be set -

for example,
...
when matched then
update set
tgt.retired_date = nvl2(tgt.retired_date, src.retired_date,null)
will set tgt.retired_date to src.retired_date if it is not null and null if it is null
0
 
slightwv (䄆 Netminder) Commented:
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
awking00Commented:
The following shows where the tgt value is null it remains null and when it's not it's updated with the src value
SQL> select * from tgt;
        ID COL1
---------- ----
         1
         2 def
SQL> select * from src;
        ID COL1
---------- ----
         1 abc
         2 ghi

SQL> merge into tgt
  2  using
  3  src
  4  on (tgt.id = src.id)
  5  when matched then
  6  update set
  7  tgt.col1 = nvl2(tgt.col1,src.col1,null);

2 rows merged.

Elapsed: 00:00:00.05
SQL> select * from tgt;
        ID COL1
---------- ----
         1
         2 ghi
0
 
DevSupportAuthor Commented:
I'm not sure if I understood correctly but, are you saying that if src.retired_date=NULL then tgt.retired_date will get set to NULL?

I actually DONT want to set tgt.retired_date to NULL if src.retired_date is NULL,

Instead I want to leave tgt.retired_date to whatever it was earlier if src.retired_date is NULL.

Please let me know if you need anymore clarification.

Thanks
0
 
slightwv (䄆 Netminder) Commented:
>>  7  tgt.col1 = nvl2(tgt.col1,src.col1,null);

If the 3rd parameter of NVL2 is null, just use NVL.
0
 
slightwv (䄆 Netminder) Commented:
>> Please let me know if you need anymore clarification.

Can you create a simplified test case with sample data and expected results?
0
 
DevSupportAuthor Commented:
I want to update tgt.retired_date to src.retired_date only if src.retired_date is not NULL and has some value.
0
 
DevSupportAuthor Commented:
Scenario:

Table tgt

Name            retired_date

ABC                11-3-2016
EFG                 10-01-2016


Table src

Name            retired_date

ABC               NULL
EFG                 10-13-2016


Result of select * from tgt  after merge of tgt and src

Name           retired_date
ABC               11-3-2016
EFG                10-13-2016


Please let me know if you need more scenarios

Thanks
0
 
slightwv (䄆 Netminder) Commented:
Why not just select from the SRC where the column isn't null?

Try this:
merge into tgt
	using (select name,retired_date from src where retired_date is not null) src
	on (tgt.name=src.name)
	when matched then
		update set tgt.retired_date=src.retired_date
/

Open in new window

0
 
DevSupportAuthor Commented:
Thank You but the columns I am comparing is not just retired_date, I would like to have  this comparison to happen for all columns (each value in the merging row needs to be compared for NULL)
0
 
DevSupportAuthor Commented:
sorry im not a oracle expert so I would appreciate if you could give me the full query for the statement I had given in my initial post.

Thanks
0
 
slightwv (䄆 Netminder) Commented:
Try the NVL suggestion:
merge into tgt
	using (select name,retired_date from src) src
	on (tgt.name=src.name)
	when matched then
		update set tgt.retired_date=nvl(src.retired_date,tgt.retired_date)
/

select * from tgt;
rollback;

Open in new window


What it is saying is update the tgt retired_date and if src.retired_date is null, set it back to itself.
0
 
DevSupportAuthor Commented:
Does this sound ok?

merge into tgt
      using (select name,retired_date from src where retired_date is not null AND src.APPLICATION_DESCRIPTION is not null AND src.APP_LEVEL ) src
      on (tgt.name=src.name)
      when matched then
            update set tgt.retired_date=src.retired_date
/

but then if one of the values is null then the entire row is not populated right?

Sorry and let me know if I am not clear enough
0
 
DevSupportAuthor Commented:
ok, I'll try and get back with the NVL suggestion..

Thank You!
0
 
PatHartmanCommented:
Look at my suggestion.  Substitute NVL for NZ as the function name and add in all the fields.  It shouldn't be necessary for one of us to type it all out for you.
0
 
DevSupportAuthor Commented:
yep, Thank You I am working on it now! Will keep you guys posted!
0
 
slightwv (䄆 Netminder) Commented:
>>Look at my suggestion.

Apologies Pat.  I didn't go back and re-read your post.

DevSupport, Pat's is the same as mine and he should get the points.
0
 
awking00Commented:
>>If the 3rd parameter of NVL2 is null, just use NVL.<<
The problem is if the tgt data is null, it remains null, and if it's not it's updated with the src data. What two parameters would you pass to the nvl function?
nvl(tgt.data,src.data) would update the null tgt value when it needs to remain null.
nvl(src.data,tgt.data) would do nothing only when the tgt data is null.

Here is the entire script -
MERGE INTO apex.DATACOLLECTION tgt 
 USING ( select * from APEX.DATACOLLECTION_TEMP
     minus
  select * from APEX.DATACOLLECTION) src 
  on (tgt.SERVERNAME = src.SERVERNAME)
  when matched then
update set tgt.RETIREDDATE = nvl2(tgt.RETIREDDATE,src.RETIREDDATE,NULL),
 tgt.APPLICATION_DESCRIPTION = nvl2(tgt.APPLICATION_DESCRIPTION,src.APPLICATION_DESCRIPTION,NULL),
 tgt.APP_LEVEL = nvl2(tgt.APP_LEVEL,src.APP_LEVEL,NULL),
 tgt.PRPC_VERSION = nvl2(tgt.PRPC_VERSION,src.PRPC_VERSION,NULL),
 tgt.TEST_GROUP = nvl2(tgt.TEST_GROUP,src.TEST_GROUP,NULL),
 tgt.APP_ENGINE = nvl2(tgt.APP_ENGINE,src.APP_ENGINE,NULL),
 tgt.APP_JAVA_VERSION = nvl2(tgt.APP_JAVA_VERSION,src.APP_JAVA_VERSION,NULL),
 tgt.APP_OWNER = nvl2(tgt.APP_OWNER,src.APP_OWNER,NULL),
 tgt.APP_PRIMARY_URL = nvl2(tgt.APP_PRIMARY_URL,src.APP_PRIMARY_URL,NULL),
 tgt.APP_SECURE_URL = nvl2(tgt.APP_SECURE_URL,src.APP_SECURE_URL,NULL),
 tgt.APP_F5_URL = nvl2(tgt.APP_F5_URL,src.APP_F5_URL,NULL),
 tgt.APP_TOMCATMANAGER = nvl2(tgt.APP_TOMCATMANAGER,src.APP_TOMCATMANAGER,NULL),
 tgt.FUNCTION = nvl2(tgt.FUNCTION,src.FUNCITON,NULL),
 tgt.DBSERVER = nvl2(tgt.DBSERVER,src.DBSERVER,NULL),
 tgt.DBNAME = nvl2(tgt.DBNAME,src.DBNAME,NULL),
 tgt.DBVERSION = nvl2(tgt.DBVERSION,src.DBVERSION,NULL),
 tgt.OS = nvl2(tgt.OS,src.OS,NULL),
 tgt.CREATE_DATE = nvl2(tgt.CREATE_DATE,src.CREATE_DATE,NULL),
 tgt.LOCATION = nvl2(tgt.LOCATION,src.LOCATION,NULL),
 tgt.LICENSE = nvl2(tgt.LICENSE,src.LICENSE,NULL),
 tgt.CPU = nvl2(tgt.CPU,src.CPU,NULL),
 tgt.RAM = nvl2(tgt.RAM,src.RAM,NULL),
 tgt.STORAGE = nvl2(tgt.STORAGE,src.STORAGE,NULL),
 tgt.JVM_INITIAL = nvl2(tgt.JVM_INITIAL,src.JVM_INITIAL,NULL),
 tgt.JVM_MAX = nvl2(tgt.JVM_MAX,src.JVM_MAX,NULL),
 tgt.SSL_CERT_EXP_DATE = nvl2(tgt.SSL_CERT_EXP_DATE,src.SSL_CERT_EXP_DATE,NULL),
 tgt.SINGLESIGNON = nvl2(tgt.SINGLESIGNON,src.SINGLESIGNON,NULL),
 tgt.URL_OBFUSCATION = nvl2(tgt.URL_OBFUSCATION,src.URL_OBFUSCATION,NULL),
 tgt.IP_ADDRESS = nvl2(tgt.IP_ADDRESS,src.IP_ADDRESS,NULL),
 tgt.SMA_LOCKDOWN = nvl2(tgt.SMA_LOCKDOWN,src.SNA_LOCKDOWN,NULL);

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
>>The problem is if the tgt data is null, it remains null

I believe that is backwards.  If you look at the sample posted in #41872291 it shows if the src is null, keep the tgt value.
0
 
awking00Commented:
Forgive me for not understanding the question properly. I was of the belief that there was concern for the tgt data being null, which is obviously not the case. I take back everything I said. :-(

0
 
DevSupportAuthor Commented:
Just a related question,

IF I use NVL2(src,retired_date, src.retired_date, tgt.retired_date)

will this work?

If so

when src.retired date is NULL then tgt.retired_date is not NULL then tgt.retired_date is the target value

when src.retired_date is not NULL and tgt.retired_date is NULL then src.retired_date replaces tgt.retired value

when src.retired_date is NOT NULL and tgt.retired_date is not NULL then src.retired_date replaces tgt.retired_date

am I right?

Thanks
0
 
slightwv (䄆 Netminder) Commented:
>>will this work?

The better question is:  Why does NVL not work?

You might be able to use NVL2 but why make things more complicate than they need to be?
0
 
DevSupportAuthor Commented:
There is a scenario wherein I want to replace tgt.retired_date to src.retired_date if tgt.retired_date is NULL and src.retired_date is not NULL. Sorry that was not in the scenario which I mentioned earlier as I didnt think of it and I thought NVL2 can handle that.
0
 
slightwv (䄆 Netminder) Commented:
>>There is a scenario wherein I want to replace tgt.retired_date to src.retired_date if tgt.retired_date is NULL and src.retired_date is not NULL.

I believe the code you have with just NVL takes care of that.

That code says if SRC.retired_date is not null, update TGT.retired_date with the src value.
0
 
DevSupportAuthor Commented:
Its working as expected for me, Thank You PatHartman, slightwv, awking00 for helping me out.
Closing this question..
0
 
PatHartmanCommented:
You're welcome.  Please close the question and award points.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 11
  • 9
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now