[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
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
Medium Priority
?
114 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 9
  • 4
  • +1
28 Comments
 
LVL 39

Accepted Solution

by:
PatHartman earned 1000 total points
ID: 41872240
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 32

Expert Comment

by:awking00
ID: 41872246
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 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 total points
ID: 41872249
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 32

Expert Comment

by:awking00
ID: 41872273
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
ID: 41872279
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 77

Expert Comment

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

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

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41872283
>> 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
ID: 41872285
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
ID: 41872291
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41872303
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
ID: 41872315
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
ID: 41872317
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 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 total points
ID: 41872341
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
 

Author Comment

by:DevSupport
ID: 41872342
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
ID: 41872346
ok, I'll try and get back with the NVL suggestion..

Thank You!
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 41872352
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
ID: 41872361
yep, Thank You I am working on it now! Will keep you guys posted!
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41872364
>>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 32

Expert Comment

by:awking00
ID: 41872378
>>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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41872384
>>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 32

Expert Comment

by:awking00
ID: 41872393
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
ID: 41872432
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41872444
>>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
ID: 41872461
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41872470
>>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
ID: 41874171
Its working as expected for me, Thank You PatHartman, slightwv, awking00 for helping me out.
Closing this question..
0
 
LVL 39

Expert Comment

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

Featured Post

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

649 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