1

I have two tables:

  • NewData:

    EName               Job        Sal
    
    smith               clerk      2000
    allen               sales      2000
    jones.domain.com    Manager    6000
    
  • OldData:

    EMPNO        ENAME                  JOB         HIREDATE       SAL
    
    7369         smith.domain.com       clerk       17-DEC-80      1300
    7549         allen.domain.com       sales       01-JAN-81      1800
    7645         jones.domain.com       Manager     01-JAN-80      5000
    

I want to update columns Job and Sal of the table OldData from table NewData by using column EName. I tried it with LIKE and SUBSTR but not getting a good solution using them.

LIKE query- UPDATE emp_backup SET emp_backup.sal = (select t1.sal from t1 join emp_backup on t1.ename like emp_backup.ename+'%')

SUBSTR query- UPDATE emp_backup SET emp_backup.sal = (select t1.sal from t1 join emp_backup on SUBSTR(t1.ename,1,4)=SUBSTR(emp_backup.ename,1,4));

  • 1
    Please show us the update statement you have already tried and what wasn't working as you expected. – BriteSponge Jun 30 '16 at 10:38
  • I tried two update statement one with LIKE and another with SUBSTR as follows, 1.UPDATE emp_backup SET emp_backup.sal = (select t1.sal from t1 join emp_backup on t1.ename like emp_backup.ename+'%') It shows error, ORA-01722: invalid number 2.UPDATE emp_backup SET emp_backup.sal = (select t1.sal from t1 join emp_backup on SUBSTR(t1.ename,1,4)=SUBSTR(emp_backup.ename,1,4)); It is giving output but it's not good solution because it is comparing only first 4 characters so if there are some records which has first 4 characters same then it will not update the data properly. – Yuvraj Jun 30 '16 at 12:36
  • 1
    Please add the statements by editing the question, not in the comments. – mustaccio Jun 30 '16 at 15:41
3

This should update for you as expected. Apologies for the number of character functions - I'm sure it could be improved via a REGEXP_SUBSTR call but I'm afraid I'm a bit busy at the moment.

MERGE INTO olddata old
USING (SELECT * FROM newdata) new 
ON (  old.ename = new.ename
   OR substr(old.ename,1,length(old.ename)-length(substr(old.ename,instr(old.ename,'.'),length(old.ename)))) = new.ename)
WHEN MATCHED THEN UPDATE SET old.salary = new.salary, old.job = new.job

The first qualifier in the ON clause will match any that have the same format name and the second will take the string from OLDDATA.ENAME before the first dot to compare it to the NEWDATA.ENAME that doesn't contain the domain name. Hope this works for you (it did for my quick tests on the data you supplied)

EDIT: Here's my simple test:

  1* SELECT * FROM olddata
SQL> /

     EMPNO ENAME                          JOB                      SALARY HIREDATE
---------- ------------------------------ -------------------- ---------- ---------
      7369 smith.domain.com               clerk                      1300 17-DEC-80
      7645 jones.domain.com               Manager                    5000 01-JAN-80
      7549 allen.domain.com               sales                      1800 01-JAN-81

SQL> SELECT * FROM newdata
  2  /

ENAME                          JOB                      SALARY
------------------------------ -------------------- ----------
jones.domain.com               Manager                    6000
allen                          sales                      2000
smith                          clerk                      2000

SQL> MERGE INTO olddata old
  2  USING (SELECT * FROM newdata) new
  3     ON (  old.ename = new.ename
  4        OR substr(old.ename,1,length(old.ename)-length(substr(old.ename,instr(old.ename,'.'),length(old.ename)))) = new.ename)
  5  WHEN MATCHED THEN UPDATE SET old.salary = new.salary, old.job = new.job
SQL> /

3 rows merged.

SQL> SELECT * FROM olddata
  2  /

     EMPNO ENAME                          JOB                      SALARY HIREDATE
---------- ------------------------------ -------------------- ---------- ---------
      7369 smith.domain.com               clerk                      2000 17-DEC-80
      7645 jones.domain.com               Manager                    6000 01-JAN-80
      7549 allen.domain.com               sales                      2000 01-JAN-81

SQL>

EDIT: Thanks to @Guarava for pointing out that the string operations can easily be removed. I was just overthinking it. The following MERGE is much more concise;

MERGE INTO olddata old
USING (SELECT * FROM newdata) new
ON (  old.ename = new.ename
   OR old.ename LIKE new.ename||'.%')
WHEN MATCHED THEN UPDATE SET old.salary = new.salary, old.job = new.job
  • I try to avoid using substring operations based on length. Can you please help to review answer posted my me? – Gaurava Agarwal Jul 1 '16 at 7:35
  • 1
    Yes, above SQL statement is working fine with me and it helped me to understand the concept of MERGE too. Thanks. – Yuvraj Jul 1 '16 at 7:45
  • 1
    @Yuvraj - Enjoy the MERGE. In my opinion of the simplest and most versatile statements and much under used. When you are happy that your question is answered mark the answer as correct so that others searching for similar questions will know which of the answers satisfied the question. – BriteSponge Jul 1 '16 at 9:26
  • Happy to have contributed to best answer in my first answer. – Gaurava Agarwal Jul 1 '16 at 16:14
0

Here you go:

update OldData a
set (a.job, a.sal) = (select b.job, b.sal 
                                  from NewData b 
                                  where b.EName = a.EName)
  • 1
    Actually, my OldData have pattern for Ename as 'Name.domain.com' for all records but in NewData, Ename column have some records just names and some as 'Name.domain.com' So, Its hard to compare b.Ename = a.Ename. – Yuvraj Jun 30 '16 at 12:43
  • 4
    @Phil, I suggest you add a (huge, bold) notice that for all rows of table OldData that the ename does not appear in table NewData, the job and sal will be updated to NULL. – ypercubeᵀᴹ Jun 30 '16 at 13:12
  • @Phil, I got extended version of your solution. – Gaurava Agarwal Jul 1 '16 at 6:40
0

if Ename columns are unique, this should work

; with old as 
( 
select job 
    , Sal       
    , reverse (
         substring(reverse(ename),charindex('.', reverse(ename), charindex('.',REVERSE(ename) )+1)+1, 50)
         ) as OldIdName     
from OldData 
) ,     new as     
(      
select Job
    , Sal    
    , reverse (
         substring(reverse(ename),charindex('.', reverse(ename), charindex('.',REVERSE(ename) )+1)+1, 50)
         ) as NewIdName     
from NewData
) 

update old     
set old.job = new.job
    , old.Sal = new.Sal      
from old      
inner join new ON OldIdName = NewIdName ;
  • 3
    I think the OP is after an Oracle solution. – BriteSponge Jun 30 '16 at 13:53
0

I try to avoid too much logic based on string operations. I think data can be divided in to two parts.

One part

       where (a.Ename=b.Ename) 

Other part

       where (a.EName like b.EName||'.%') 

Here is the simple query I test works good:

      update OldData a
      set (a.job, a.sal) = (select b.job, b.sal 
                              from NewData b 
                              where (b.EName = a.EName) or (a.EName like b.EName||'.%'));  
  • 1
    @Guarava - as per the answer you based this on you will end up updating columns 'job' and 'sal' to null for any records in olddata that are not in newdata. However I don't like lots of string operations nested either and the (a.EName like b.EName||'.%') works much better. – BriteSponge Jul 1 '16 at 8:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.