Oracle – Update Table Data from Another Table with Similar Column Strings

likeoracletableupdate

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

Best Answer

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
Related Question