一个用户下的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
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
解决方案 »
- oracle提示找不到被锁定用户怎么办?
- 急!ORACLE数据查询问题
- 数据库中的表空间给误删除掉了,但是数据文件还在,请问能恢复这个表空间吗?
- 急,高分求正解:请教几个ORACLE10G问题,有结果后即结贴给分
- C#访问package的一个存储过程出错
- 一个查询语句的小问题
- 如何去掉表前面得用户前缀?
- 有没有什么可以导出数据库中所有表表结构的软件呀?
- 数据传输问题----急!!
- 有哪位知道怎么将Informix上的数据库数据导入Oracle
- 各位大哥大姐,我想在BAT中获取当前时间前三个月的日期,这个脚本该怎么写
- 我本地库D1用户A需要执行远程库D2上的一个用户B2的存储过程P2,如何搞啊?
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
);
SET OBJECT_NAME=replace(OBJECT_NAME,'.','-')
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