Ora-00933 Error On Update
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about ora-00933 sql command not properly ended in oracle Stack Overflow the company Business Learn more about hiring developers or posting ads with
Ora-00933 Sql Command Not Properly Ended Select
us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is ora-00933 sql command not properly ended insert a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up SQL Error: ORA-00933: SQL command not properly ended up vote 8 down vote pl/sql: ora-00933: sql command not properly ended favorite 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
Ora-00933 Sql Command Not Properly Ended In Java
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 sql oracle share|improve this question edited Jan 20 '12 at 11:10 Mark Bannister 34.4k32450 asked Jan 20 '12 at 11:07 Microsoft DN 5,09132345 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 I tried your solution, but not working for me –Microsoft DN Jan 20 '12 at 11:16 add a comment| 5 Answers 5 active oldest votes up vote 9 down vote accepted 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 share|improve this answer answered Jan 20 '12 at 12:58 a_horse_with_no_name 187k24235312 add a comment| up vote 2 down vote Not exactly the case of actual context of this question... But thi
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers ora-00933 sql command not properly ended group by or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question ora-00933: sql command not properly ended delete x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them;
Ora-00933 Update
it only takes a minute: Sign up Oracle: UPDATE statement causes ORA-00933 error up vote 1 down vote favorite Can anyone see what's wrong with this statement? I've tried for a while to fix it but to no avail, and http://stackoverflow.com/questions/8940471/sql-error-ora-00933-sql-command-not-properly-ended the answers I've found on the web insist I need to remove an ORDERBY statement, something I'm not using. BEGIN UPDATE Tags SET MaskReasonId = v_MaskReasonId, AppliedTime = v_AppliedTime, ExpireTime = v_ExpireTime, UserId = v_UserId, ClientNode = v_ClientNode, CommentId = v_CommentId FROM Tags WHERE Tag = v_sTag OR Tag = v_Tag; END; oracle share|improve this question edited Aug 28 '12 at 8:09 asked Aug 28 '12 at 7:55 Skulmuk 1871317 2 You cannot use a join in an update http://stackoverflow.com/questions/12154984/oracle-update-statement-causes-ora-00933-error statement. –Ben Aug 28 '12 at 7:57 Cheers for the quick response @Ben. I've removed the INNER JOIN but I'm still getting the error. –Skulmuk Aug 28 '12 at 7:59 Did you also update your where clause after removing join.. ? –heretolearn Aug 28 '12 at 8:01 Yup, I'm now just checking WHERE Node = v_Node; EDIT: Just realised I'm no longer joining that table onto this one. I've swapped over to the Tag where clause but still no joy –Skulmuk Aug 28 '12 at 8:05 From tag is also not supported by oracle. –heretolearn Aug 28 '12 at 8:14 add a comment| 3 Answers 3 active oldest votes up vote 1 down vote accepted Oracle does not support the "FROM" construct in the update statement, try this: BEGIN UPDATE Tags SET MaskReasonId = v_MaskReasonId, AppliedTime = v_AppliedTime, ExpireTime = v_ExpireTime, UserId = v_UserId, ClientNode = v_ClientNode, CommentId = v_CommentId WHERE EXISTS (SELECT * FROM Tags WHERE Tag = v_sTag OR Tag = v_Tag); END; share|improve this answer answered Aug 28 '12 at 8:12 heretolearn 3,13621526 Perfect, thanks @sshekhar! –Skulmuk Aug 28 '12 at 8:14 add a comment| up vote 3 down vote Oracle doesn't support UPDATE FROM statement but supports UPDATE SELECT. Try this. For this query Nodes.NodeID must be a primary key in Nodes table. I guess v_MaskReasonId,... are fields from NODES(?) : update (
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books Oracle Scripts Ion Excel-DB Don Burleson Blog
http://www.dba-oracle.com/t_ora_00933_sql_command_ended.htm ORA-00933: SQL command not properly ended Question: on ORA-00933: SQL command not properly ended CODE UPDATE PEOPLE SET PEOPLE.SURNAME = HR_DATA_IMPORT.SURNAME INNER JOIN PEOPLE ON HR_DATA_IMPORT.NI.NO = http://itknowledgeexchange.techtarget.com/itanswers/how-do-i-solve-the-ora-00933-sql-command-not-properly-ended-error-message/ PEOPLE.NI_NUMBER; ORA-00933: SQL command not properly ended ORA-00933: SQL command not properly ended Cause: The SQL statement ends with an inappropriate clause. For example, an ORDER BY clause may have been included in a CREATE sql command VIEW or INSERT statement. ORDER BY cannot be used to create an ordered view or to insert in a certain order. Action: Correct the syntax by removing the inappropriate clauses. It may be possible to duplicate the removed clause with another SQL statement. For example, to order the rows of a view, do so when querying the view and not when creating it. This error can also occur in SQL*Forms sql command not applications if a continuation line is indented. Check for indented lines and delete these spaces. Answer by Chenn: I am never able to run an update using a join. You need a subselect. Filter it by pushing your field from the main query into the subselect. I'm not sure what your NI.No field is so you may need to take a look at that... UPDATE PEOPLE a SET a.SURNAME = ( select b.SURNAME from PEOPLE b where b.NI.NO = a.NI_NUMBER ) Get the Complete Oracle SQL Tuning Information The landmark book "Advanced Oracle SQL Tuning The Definitive Reference" is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher. �� Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum. Verify experience! Anyone considering using the services of an Oracle support exper? Ask a question, help others, and get answers from the community Discussions Start a thread and discuss today's topics with top experts Blogs Read the latest tech blogs written by experienced community members How do I solve the ORA-00933: SQL command not properly ended error message? ITKE 440495 pts. Tags: Thanks! We'll email youwhen relevant content isadded and updated. Following Follow ORA Thanks! We'll email youwhen relevant content isadded and updated. Following Follow Oracle Database For the past few weeks, I've been using an OLEDB provider for ADO.NET connecting to an Oracle database. Currently in my loop, I'm doing this insert: insert into ps_tl_compleave_tbl values('2626899', 0, TO_DATE('01/01/2002', 'MM/DD/YYYY'), 'LTKN', 'LTKN', '52', TO_DATE('01/01/2002', 'MM/DD/YYYY'), 16.000000, 24.000)insert into ps_tl_compleave_tbl values('4327142', 0, TO_DATE('03/23/2002', 'MM/DD/YYYY'), 'LTKN', 'LTKN', '51', TO_DATE('03/23/2002', 'MM/DD/YYYY'), 0.000000, 0.000) The first insert works but the second one is giving me an error: ORA-00933: SQL command not properly ended What should I do? Asked: September 24, 20148:00 PM Last updated: July 29, 20167:48 AM Related Questions AS/400 date format Getting the number of days between two dates in Oracle 11g Date Format Change to dd/mm/yyyy Date dd-mm-yyyy reversed to mm-dd-yyyy Date Time Error in VB Answer Wiki Last updated: September 25, 20143:58 PM GMT carlosdl80,565 pts. History Contributors Ordered by most recent carlosdl80,565 pts. Thanks. We'll let you know when a new response is added. If you are running more than one statement, you need to end each one with a semicolon (;), otherwise Oracle sees it as one single, but incorrect, command. Also, when you run more than one command, you might need to put them inside a BEGIN-END block. If you are running more than one statement, you need to end each one with a semicolon (;), otherwise Oracle sees it as one single, but incorrect, command.Also, when you run more than one command, you might need to put them inside a BEGIN-END block. Please enter an answer. Send me notifications when members answer or reply to this question. Register Hereor login if you are already a member E-mail User Name Password Forgot Password? By submitting you agree