Solved

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

Posted on 2016-11-02
28
54 Views
Last Modified: 2016-11-08
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
Comment
Question by:DevSupport
  • 11
  • 9
  • 4
  • +1
28 Comments
 
LVL 34

Accepted Solution

by:
PatHartman earned 250 total points
Comment Utility
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
 
LVL 31

Expert Comment

by:awking00
Comment Utility
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
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
Comment Utility
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
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
 

Author Comment

by:DevSupport
Comment Utility
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>  7  tgt.col1 = nvl2(tgt.col1,src.col1,null);

If the 3rd parameter of NVL2 is null, just use NVL.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>> Please let me know if you need anymore clarification.

Can you create a simplified test case with sample data and expected results?
0
 

Author Comment

by:DevSupport
Comment Utility
I want to update tgt.retired_date to src.retired_date only if src.retired_date is not NULL and has some value.
0
 

Author Comment

by:DevSupport
Comment Utility
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
 

Author Comment

by:DevSupport
Comment Utility
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
 

Author Comment

by:DevSupport
Comment Utility
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
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:DevSupport
Comment Utility
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
 

Author Comment

by:DevSupport
Comment Utility
ok, I'll try and get back with the NVL suggestion..

Thank You!
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 

Author Comment

by:DevSupport
Comment Utility
yep, Thank You I am working on it now! Will keep you guys posted!
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
 
LVL 31

Expert Comment

by:awking00
Comment Utility
>>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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
 
LVL 31

Expert Comment

by:awking00
Comment Utility
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
 

Author Comment

by:DevSupport
Comment Utility
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
 

Author Comment

by:DevSupport
Comment Utility
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
 

Author Comment

by:DevSupport
Comment Utility
Its working as expected for me, Thank You PatHartman, slightwv, awking00 for helping me out.
Closing this question..
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
You're welcome.  Please close the question and award points.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now