我原来在SQL Server区问的(看错区了,呵呵),两位热心的朋友告诉我的都不行,他们是这样说的:
update register
set customerid=right('0000'+rtrim(c.BRANCH_NO),4)+right(c.CUSTOM_NO,8)
from f_cust c
where register.iDCard is not null
and register.iDCard=c.CAupdate r
set customerid=
CASE len(rtrim(c.BRANCH_NO))
when 1 then '000'+rtrim(c.BRANCH_NO)+right(c.CUSTOM_NO,8)
when 2 then '00'+rtrim(c.BRANCH_NO)+right(c.CUSTOM_NO,8)
when 3 then '0'+rtrim(c.BRANCH_NO)+right(c.CUSTOM_NO,8)
ELSE rtrim(c.BRANCH_NO)+right(c.CUSTOM_NO,8)
END
from register r,f_cust c
where r.iDCard is not null
and r.iDCard=c.CA可是我都报错:ORA-00933:SQL command not properly ended
update register
set customerid=right('0000'+rtrim(c.BRANCH_NO),4)+right(c.CUSTOM_NO,8)
from f_cust c
where register.iDCard is not null
and register.iDCard=c.CAupdate r
set customerid=
CASE len(rtrim(c.BRANCH_NO))
when 1 then '000'+rtrim(c.BRANCH_NO)+right(c.CUSTOM_NO,8)
when 2 then '00'+rtrim(c.BRANCH_NO)+right(c.CUSTOM_NO,8)
when 3 then '0'+rtrim(c.BRANCH_NO)+right(c.CUSTOM_NO,8)
ELSE rtrim(c.BRANCH_NO)+right(c.CUSTOM_NO,8)
END
from register r,f_cust c
where r.iDCard is not null
and r.iDCard=c.CA可是我都报错:ORA-00933:SQL command not properly ended
解决方案 »
- 关于pl/sql动态声明变量的问题
- OracleDBConsole服务启动不了,又该怎么删除oracle中的EM 资料档案库?
- 使用ORACLE建立一个函数,但是无法调用。提示:程序包或函数处于无效状态
- 请教个问题,Oracle数据库上多了个触发器,怎么查到这个触发器是谁建的?
- 求一sql
- ---c# 怎么读出 和 写入 Oracle里 long 类型的字段???
- 大侠请进,导入数据库时出错。IMP-00058
- Oracle数据库表名保留字问题
- 请问一套oracle 8i和9i正版软件需要多少钱啊?
- 关于if exists .... then ... else... sql 语句在oracle中能否实现的问题!
- 简单sql语句?
- sql*plus中怎样执行有返回记录集的存储过程?
update 表名 set 列名 where (条件)
你用这个写法试试?
update register a
set a.customerid=right('0000'+rtrim(c.BRANCH_NO),4)+right(c.CUSTOM_NO,8)
where (select a.customerid from f_cust c
where register.iDCard=c.CA and register.iDCard is not null
update register a
set customerid=select lpad(branch_no,4,'0')||substr(custom_no,-1,8) from f_cust where a.CA=IDCard;