日期:2014-05-17  浏览次数:20968 次

一段关于orcle数据库处理的SQL命令!
原先对MSSQL数据库进行一些操作,用到用下SQL语句,没问题。
但是最近因为工作需要,数据库转换成了orcle的,再执行如下语句就不行了,总是提示未正确结束,我把“go”去掉,逐条运行也不行,提示没正确的表达式。
请帮忙看看如何改成orcle能顺利运行的语句。
update cert set double_flag='1'
where double_certsn is null and cert_status='use' and ctml_name like '%签名%' 
go

update cert set double_flag='2'
where double_certsn is null and cert_status='use' and ctml_name like '%加密%' 
go

update cert set double_certsn=convert(varchar(50),certsn)+'0000'
where double_certsn is null and cert_status='use' and ctml_name like '%签名%' 
go

UPDATE A SET
  double_certsn=B.double_certsn
FROM cert AS A
  JOIN cert AS B
  ON A.subject=B.subject
WHERE B.double_certsn IS NOT NULL
  AND A.double_certsn IS NULL
  AND A.cert_status='use'
go



------解决方案--------------------
另:oracle的UPDATE FROM 语法与 SQL SERVER 的语法不一样。
我个人比较喜欢这样的写法:
UPDATE
(SELECT rolling_code_id
FROM tb_client_win_lost_report a,
temp_role_id b
WHERE a.role_id =b.id
AND rolling_code_id=1
) a
SET a.rolling_code_id=2;