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

Question

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 1

Semicolon ; on the end of command had caused the same error on me.

cmd.CommandText = "INSERT INTO U_USERS_TABLE (USERNAME, PASSWORD, FIRSTNAME, LASTNAME) VALUES ("                 + "'" + txtUsername.Text + "',"                 + "'" + txtPassword.Text + "',"                 + "'" + txtFirstname.Text + "',"                 + "'" + txtLastname.Text + "');"; <== Semicolon in "" is the cause.                                                       Removing it will be fine. 

Hope it helps.

Answer 2

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:

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