Home » SQL & PL/SQL » SQL & PL/SQL » UPDATE ERRO ORA-00933
UPDATE ERRO ORA-00933 [message #209634] Sat, 16 December 2006 01:32 Go to next message
zxx2403
Messages: 12
Registered: November 2006
Location: china
Junior Member

HI:

I'm getting "Error ORA-00933, SQL Command not properly ended " at the line w/t "FROM" statement in the SQL below. I'm using Oracle 9.2.0.5

What am I doing wrong here

update test1 a

set field1 =
(
select b.a1 from test2 b where b.f1 = a.f1
)
-
(
select b.a1 from test3 c where c.f1 = a.f1
)

THANKS A LOT

[Updated on: Sat, 16 December 2006 02:11]

Report message to a moderator

Re: UPDATE ERRO ORA-00933 [message #209655 is a reply to message #209634] Sat, 16 December 2006 07:07 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You can not subtract two QUERIES. Something like this might help:
update test1 a set
  field1 = (select x.first - y.second
            from (select b.a1 first
                  from best2 b
                  where b.f1 = a.f1
                 ) x,
                 (select b.a1
                  from test3 c
                  where c.f1 = a.f1
                 ) y
           );
Re: UPDATE ERRO ORA-00933 [message #210212 is a reply to message #209655] Tue, 19 December 2006 19:47 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Oh, the perils of untested code! Littlefoot's code will also result in an error because the reference to a.f1 is nested too deep in a subquery to reference it. Please see the demonstration below, where I have offered a couple of solutions at the end.

scott@ORA92> SELECT * FROM v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE	9.2.0.1.0	Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

scott@ORA92> CREATE TABLE test1
  2    (field1 NUMBER,
  3  	f1     NUMBER)
  4  /

Table created.

scott@ORA92> CREATE TABLE test2
  2    (a1     NUMBER,
  3  	f1     NUMBER)
  4  /

Table created.

scott@ORA92> CREATE TABLE test3
  2    (a1     NUMBER,
  3  	f1     NUMBER)
  4  /

Table created.

scott@ORA92> -- reproduction of original error:
scott@ORA92> update test1 a
  2  set field1 =
  3  (
  4  select b.a1 from test2 b where b.f1 = a.f1
  5  )
  6  -
  7  (
  8  select c.a1 from test3 c where c.f1 = a.f1
  9  )
 10  /
-
*
ERROR at line 6:
ORA-00933: SQL command not properly ended


scott@ORA92> -- Littlefoot's code:
scott@ORA92> update test1 a set
  2    field1 = (select x.first - y.second
  3  		 from (select b.a1 first
  4  		       from best2 b
  5  		       where b.f1 = a.f1
  6  		      ) x,
  7  		      (select b.a1
  8  		       from test3 c
  9  		       where c.f1 = a.f1
 10  		      ) y
 11  		)
 12  /
                  where c.f1 = a.f1
                               *
ERROR at line 9:
ORA-00904: "A"."F1": invalid identifier


scott@ORA92> -- solutions:
scott@ORA92> UPDATE test1 a
  2  SET    field1 =
  3  	    (SELECT b.a1 - c.a1
  4  	     FROM   test2 b, test3 c
  5  	     WHERE  b.f1 = a.f1
  6  	     AND    c.f1 = a.f1)
  7  /

0 rows updated.

scott@ORA92> UPDATE test1 a
  2  SET    field1 =
  3  	    (SELECT (SELECT b.a1 FROM test2 b WHERE b.f1 = a.f1)
  4  		    -
  5  		    (SELECT c.a1 FROM test2 c WHERE c.f1 = a.f1)
  6  	     FROM   DUAL)
  7  /

0 rows updated.

scott@ORA92>


Previous Topic: DATE help (adding 7 hours to a date)
Next Topic: how to retrieve sysdate in oracle?senthil
Goto Forum:
  


Current Time: Thu Dec 08 04:23:07 CST 2016

Total time taken to generate the page: 0.08522 seconds