Oracle SQL-更新第一个子字符串

您可以使用 UPDATE 声明以及 EXISTS 在 WHERE 条件 例如

UPDATE vpn v
   SET vpn = ( SELECT u.vpn||SUBSTR(v.vpn,INSTR(v.vpn,'-')) 
                 FROM updated_vpn u 
                WHERE u.item = v.item  )
 WHERE EXISTS ( SELECT 0 FROM updated_vpn u WHERE u.item = v.item  )  

Demo

或者使用 MERGE 声明 MATCHED 选项也可以使用,例如

MERGE INTO vpn v 
     USING updated_vpn u
        ON ( v.item = u.item )
      WHEN MATCHED THEN UPDATE SET v.vpn = u.vpn||SUBSTR(v.vpn,INSTR(v.vpn,'-'))

Demo

样本数据:

create table vpns (item varchar2(10), vpn varchar2(30));
create table updated_vpns (item varchar2(10), vpn varchar2(30));

insert all
    into vpns values ('A124343', '007-46-307-RED MEDIUM')
    into vpns values ('A154363', '008-25-203-YELLOW MEDIUM')
    into updated_vpns values ('A124343', '024')
    into updated_vpns values ('A154363', '041')
select * from dual;

Update语句(也可以使用merge):

update vpns v
set    v.vpn =
       ( select regexp_replace(v.vpn, '([^-]+)', u.vpn, 1, 2)
         from   updated_vpns u
         where  u.item = v.item );

结果:

ITEM       VPN
---------- ------------------------------
A124343    007-024-307-RED MEDIUM
A154363    008-041-203-YELLOW MEDIUM