Microsoft DN Microsoft DN - 1 year ago 162
SQL Question

SQL Error: ORA-00933: SQL command not properly ended

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"

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

Answer Source

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

Something like this:

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

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download