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

• BriteSponge: Please show us the update statement you have already tried and what wasn't working as you expected.
• Yuvraj: 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.
• mustaccio: Please add the statements by editing the question, not in the comments.

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

• Gaurava Agarwal: I try to avoid using substring operations based on length. Can you please help to review answer posted my me?
• Yuvraj: Yes, above SQL statement is working fine with me and it helped me to understand the concept of MERGE too. Thanks.
• BriteSponge: @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.
• Gaurava Agarwal: Happy to have contributed to best answer in my first answer.

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)

• Yuvraj: 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.
• ypercubeᵀᴹ: @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.
• Gaurava Agarwal: @Phil, I got extended version of your solution.

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 ;

• BriteSponge: I think the OP is after an Oracle solution.

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

• BriteSponge: @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.