Update data of one table from another with comparing columns have same initial strings

Question

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));

Answer 1

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

Answer 2

Here you go:

update OldData a
set (a.job, a.sal) = (select b.job, b.sal 
                                  from NewData b 
                                  where b.EName = a.EName)

Answer 3

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 ;

Answer 4

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||'.%'));