请教高手帮我完成这个UPDATE语句，最好代码简洁，非常感谢！

Question

一个用户下的2张表
hsp_entity(entity_id)
hsp_object(object_id,object_name)
找出entity_id与object_id相匹配的记录，然后UPDATE这些记录的object_name原来的object_name如下，我想把所有的“.”替换成“-”,请注意第一条记录后面没有“.”
C014304
C014304.C014300
C014300.I014300
C014300.I010812
C014300.I000105
C014304.I011300
C014304.C014305
C014305.I007800
C014305.I007900我使用了这样的语句
UPDATE HSP_OBJECT
SET OBJECT_NAME=CONCAT(CONCAT(SUBSTR(OBJECT_NAME,1,7),'-'),SUBSTR(OBJECT_NAME,9,30))
WHERE HSP_OBJECT.OBJECT_ID IN
(
SELECT OBJECT_ID
  FROM hsp_object,hsp_entity
WHERE entity_id=object_id
);

但是得到了这样的结果，其他记录都对，第一条记录多了个“-”的尾巴，请问如何解决，或者有没有别的写法
C014304-
C014304-C014300
C014300-I014300
C014300-I010812
C014300-I000105
C014304-I011300
C014304-C014305
C014305-I007800
C014305-I007900

Answer 1

直接replace不就行了
UPDATE HSP_OBJECT 
SET OBJECT_NAME=replace(OBJECT_NAME,'.','-')
WHERE HSP_OBJECT.OBJECT_ID IN 
( 
SELECT OBJECT_ID 
FROM hsp_object,hsp_entity 
WHERE entity_id=object_id 
); 

Answer 2

UPDATE HSP_OBJECT 
SET OBJECT_NAME=replace(OBJECT_NAME,'.','-') 

Answer 3

create table t2(
 entity_id number
);insert into t2 
select 1 from dual union all
select 2 from dual union all
select 3 from dual union all
select 4 from dual; create table t3(
 object_id number,
 object_name varchar2(20)
);insert into t3
select 1,'C014304' from dual union all
select 2,'C014304.C014300' from dual union all
select 3,'C014300.I014300' from dual union all
select 4,'C014300.I010812' from dual;
update t3
set object_name=replace(object_name,'.','-')
where 
   object_id in (
         select object_id
         from t2,t3
         where entity_id=object_id
   );
SQL> select * from t3; OBJECT_ID OBJECT_NAME
---------- -----------------
         1 C014304
         2 C014304-C014300
         3 C014300-I014300
         4 C014300-I010812