SQL 错误:ORA-00933:SQL 命令未正确结束

2021-12-06 00:00:00 sql oracle

我正在尝试使用联接更新 oracle SQL 开发人员中的记录.以下是我的查询-

I am trying to update a record in oracle SQL developer by using Joins. Following is my query-

UPDATE system_info set field_value = 'NewValue' 
FROM system_users users 
JOIN system_info info ON users.role_type = info.field_desc 
where users.user_name = 'uname'

但是，当我尝试执行它时，出现以下错误-

However, when I tried to execute it, I got following error-

Error report: SQL Error: ORA-00933: SQL command not properly ended 
              00933. 00000 - "SQL command not properly ended"

我尝试删除 JOINS

I tried removing JOINS

UPDATE system_info info 
SET info.field_value = 'NewValue' 
FROM system_users users 
where users.user_name = 'uname' AND users.role_type = info.field_desc

但仍然有同样的错误谁能告诉我错误原因和解决方法

but still having same error can anybody tell me the error reason and solution

推荐答案

Oracle 不允许在 UPDATE 语句中连接表.你需要用一个相关的子选择重写你的语句

Oracle does not allow joining tables in an UPDATE statement. You need to rewrite your statement with a co-related sub-select

像这样:

UPDATE system_info
SET field_value = 'NewValue' 
WHERE field_desc IN (SELECT role_type 
                     FROM system_users 
                     WHERE user_name = 'uname')

有关 UPDATE 语句的(有效)语法的完整说明，请阅读手册:

For a complete description on the (valid) syntax of the UPDATE statement, please read the manual:

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10008.htm#i2067715

相关文章