使用比较列从另一个表更新数据具有相同的初始字符串。

Question

我有两张桌子：

• NewData: EName职务Sal smith职员2000 allen 2000 jones.domain.com Manager 6000
• OldData: EMPNO工作HIREDATE SAL 7369 smith.domain.com办事员17-DEC-80 1300 7549 allen.domain.com销售01-81 1800 7645 jones.domain.com Manager 01-1月-80 5000

我希望通过使用列OldData，从表NewData更新表EName的Job列。我用LIKE和SUBSTR试过了，但是没有得到一个很好的解决方案。

类似查询- UPDATE emp_backup SET emp_backup.sal =(从t1.ename上选择t1.sal连接emp_backup，如emp_backup.ename+'%')

SUBSTR查询-更新SUBSTR(t1.ename,1,4)=SUBSTR(emp_backup.ename,1,4))；上的emp_backup SET emp_backup.sal =(从t1 join emp_backup中选择t1.sal )

Answer 1

这应该会像预期的那样为您更新。对于字符函数的数量表示歉意-我相信它可以通过一个REGEXP_SUBSTR调用来改进，但恐怕我现在有点忙。

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

ON子句中的第一个限定符将匹配任何具有相同格式名称的字符串，第二个限定符将在第一个点之前从OLDDATA.ENAME获取字符串，将其与不包含域名的NEWDATA.ENAME进行比较。希望这对你有用(我对你提供的数据进行了快速测试)

编辑:以下是我的简单测试：

  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>

编辑:感谢@瓜拉瓦指出字符串操作可以很容易地移除。我只是想得过头了。下面的合并更加简洁；

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

给你：

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

Answer 3

如果Ename列是唯一的，这应该可以工作。

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