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

I think your sqlDevelepor executed the previous line. put a semi colon before and after your sql statement and try again — You Qi, Jan 20 '12 at 11:11

score 97 · Answer 1 · edited Oct 22 '15 at 23:01

97

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.

edited Oct 22 '15 at 23:01

Manos Nikolaidis

21,608
12
74
82

answered Oct 22 '15 at 22:17

ronIT

1,035
1
9
6

2

Ouch! I had this problem, but didn't realize it, because including the ";" on the SqlPlus command line works just fine. – kc2001 Apr 24 '18 at 17:54
i too had a same problem . its very hard to find it exact issue thanks it helped me a lot – vela Jun 24 '19 at 07:02
Very helpful, this was the mistake ";", Thanks – Vikram S May 10 '21 at 12:24
spent 3 hrs then saw this answer. – Amit Sinha Jun 05 '21 at 08:19

score 21 · Accepted Answer · answered Jan 20 '12 at 12:58

21

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

answered Jan 20 '12 at 12:58

Seeing this for the first time since it was lined to in a Comment (in Nov. 2016). This is not true: Oracle does allow joins in an UPDATE, but there are some restrictions (obvious ones, demanded by logic, not by Oracle). Where does this very widespread misconception that Oracle doesn't allow one to update a join come from? How the update with joins work is discussed for example here: http://stackoverflow.com/questions/37001180/update-with-join-syntax-for-oracle-database – Nov 30 '16 at 15:17
@mathguy: Oracle allows to update a derived table (sub-query) which can under certain restrictions use a join (mainly: the query needs to be a "key preserved table"). But that is something different then simply joining the target table to another table (which has typically less restrictions in those DBMS that support this) – Nov 30 '16 at 18:40
Exactly right - which is not the same as "Oracle does not allow joining tables in an UPDATE statement." I know that, and clearly you know that too; now search through SO and see how many times people make that statement, even in (many) cases when updating through a join would work perfectly fine, if only people used the proper syntax and they had the proper unique constraint in the source table. I just created a Documentation article to discuss this, we'll see if it is approved. – Nov 30 '16 at 19:59
1

Mentioned link says "Page not found" – sushil Jun 17 '20 at 12:49

score 3 · Answer 3 · edited Aug 29 '17 at 01:29

Not exactly the case of actual context of this question, but this exception can be reproduced by the next query:

update users set dismissal_reason='he can't and don't want' where userid=123

Single quotes in words can't and don't broke the string. In case string have only one inside quote e.g. 'he don't want' oracle throws more relevant quoted string not properly terminated error, but in case of two SQL command not properly ended is thrown.

Summary: check your query for double single quotes.

score 3 · Answer 4 · edited May 23 '17 at 11:54

3

Your query should look like

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

You can check the below question for help

SQL update query using joins?

edited May 23 '17 at 11:54

Community

1
1

answered Jan 20 '12 at 11:13

Zuber Surya

839
7
17

ANSI joins are supported by Oracle – Sathyajith Bhat Jan 20 '12 at 11:23
@Sathya: but not in an UPDATE statement. – Jan 20 '12 at 12:58
you can check the link (SQL update query using joins?),it has your answer – Zuber Surya Jan 20 '12 at 13:38

score -9 · Answer 5 · answered Jan 20 '12 at 12:19

-9

its very true on oracle as well as sql is "users" is a reserved words just change it , it will serve u the best if u like change it to this

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'

answered Jan 20 '12 at 12:19

abhijit

11
5

3

`users` is **not** a reserved word! `user` however is a reserved word. But the real problem is, that Oracle does not allow FROM or JOIN in an UPDATE statement. – Jan 20 '12 at 12:55

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

5 Answers5

Linked