Home » SQL & PL/SQL » SQL & PL/SQL » missing expression error with update subquery (oracle 10g, Toad 9.0.1.8)
missing expression error with update subquery [message #409437] Mon, 22 June 2009 13:32 Go to next message
gtyagi.123
Messages: 3
Registered: June 2009
Junior Member
Requirement:

Table table
id some_sequence
100 1
100 2
100 3
101 0
101 1
102 2
102 3
102 4

I need to update the sequence to start from 0 for each id. e.g for id 100 sequence values are 1,2,3 this should be updated to 0,1,2 and for 101 from 2,3,4 to 0,1,2.

Query:
update table tab1
set tab1.some_sequence = (tab1.some_sequence - select min(tab2.some_sequence) from table tab2 where tab1.id = tab2.id);

Error:
ORA-00936 missing expression

Any pointers would be appreciated.

Thanks
GT
Re: missing expression error with update subquery [message #409439 is a reply to message #409437] Mon, 22 June 2009 13:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Put tab1.some_sequence inside the select.

Regards
Michel
Re: missing expression error with update subquery [message #409443 is a reply to message #409439] Mon, 22 June 2009 14:01 Go to previous messageGo to next message
gtyagi.123
Messages: 3
Registered: June 2009
Junior Member
Thanks for the prompt response.

Unfortunately got the same error with:

update table tab1
set tab1.some_sequence = select tab1.some_sequence - min(tab2.some_sequence) from table tab2 where tab1.id = tab2.id;

Am I missing sth. here?

Thanks
GT

Re: missing expression error with update subquery [message #409445 is a reply to message #409443] Mon, 22 June 2009 14:19 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Where did you find that syntax? UPDATE TABLE tab1 SET ..., SELECT FROM TABLE tab1 ... There's no "table" in these statements, is there?

Once you fix that, as well as table name which shouldn't be there (what is "tab2"?), query might work:
UPDATE  tab1 t1
SET t1.some_sequence = (SELECT t1.some_sequence - MIN(t2.some_sequence) 
                        FROM tab1 t2 WHERE t1.id = t2.id
                       );
Re: missing expression error with update subquery [message #409447 is a reply to message #409443] Mon, 22 June 2009 14:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Use SQL*Plus and copy and paste your session.

Post a test case we can reproduce.

Regards
Michel

[Updated on: Mon, 22 June 2009 14:21]

Report message to a moderator

Re: missing expression error with update subquery [message #409448 is a reply to message #409445] Mon, 22 June 2009 14:26 Go to previous message
gtyagi.123
Messages: 3
Registered: June 2009
Junior Member
Thanks for the reply Liitlefoot and Michel. Your suggestion worked i was missing the () around second select query.

Thanks much.

[Updated on: Mon, 22 June 2009 14:26]

Report message to a moderator

Previous Topic: Mutating trigger
Next Topic: Database Link: ORA-00604, ORA-20099, ORA-06512, ORA-02063
Goto Forum:
  


Current Time: Sat Dec 03 18:08:38 CST 2016

Total time taken to generate the page: 0.11834 seconds