一则> ORA-00933: SQL command not properly ended 排查解决

SQL中正常执行的SQL语句：

update b_dispatch set ReferenceNum = (
  case when ISNULL(fno,' ')=' ' then fno when fno='〔〕' then '' else replace(k.FileNo, '%s', fno) end 
)  from b_dispatch b 
left join k_docimage k on b.ModName = k.Name  
where b.ID = '{4589EEC4-9E7F-4CBF-947E-8D47FDE325AC}' and (fno<>ReferenceNum);

在ORALCE中这样会报错：ORA-00933: SQL command not properly ended

--> ORA-00933: SQL command not properly ended
update b_dispatch set ReferenceNum = (
  case when NVL(fno,' ')=' ' then fno when fno='〔〕' then '' else replace(k.FileNo, '%s', fno) end 
)  from b_dispatch b
left join k_docimage k on b.ModName = k.Name  
where b.ID = '{4589EEC4-9E7F-4CBF-947E-8D47FDE325AC}' and (fno<>ReferenceNum);

ORACLE下正确写法：

update b_dispatch set ReferenceNum = (  
SELECT (case when NVL(fno,' ')=' ' then fno when fno='〔〕' then '' else replace(k.FileNo, '%s', fno) end ) AS SNUM from b_dispatch b
left join k_docimage k on b.ModName = k.Name  
where b.ID = '{4589EEC4-9E7F-4CBF-947E-8D47FDE325AC}' and (fno<>ReferenceNum)
) 
where id= '{4589EEC4-9E7F-4CBF-947E-8D47FDE325AC}' and (fno<>ReferenceNum);

 

sql写法：

UPDATE A 
SET ID=B.ID 
FROM A,B 
WHERE A.NAME=B.NAME

在ORACLE数据库中不支持上面的这种UPDATE写法，update后面不能使用FROM这个关键字，应该像下面这样来写才对：

UPDATE A 
SET ID=(
        SELECT ID 
        FROM B 
        WHERE A.NAME=B.NAME
       )

（ORA-00933:SQL命令没有正确的结束）导致原因：

1：可能SQL语句中关键字前后缺少空格

2：Oracle 给表起别名时，直接在表名的后面空格别名就可以，不需要AS

3：SQL 语句中缺少关键字或者多了某个关键字，比如：AND

4：SQL 语句中缺少关键字或者多了某个标点符号，比如：,