Oracle SQL syntax error (missing right parenthesis)

Question

I don't understand why this provokes a syntax error (missing right parenthesis):

UPDATE table
SET doc =
  (SELECT 'table-2844-doc' || SUBSTR(doc_file, INSTR(doc_file, '.', -1))
   FROM docvers
   WHERE (docvers.table_name = 'other_table'
          AND docvers.field_name = 'doc')
     AND ROWNUM = 1
   ORDER BY VERSION DESC)
WHERE table_id = 2844

This looks right to me, does get executed correctly in SQL Server, and is similar to requests found, for example, in Oracle SQL: Update a table with data from another table.

Any tip?

It probably doesn't do what you want (because it selects a single arbitrary row and then orders it -- which does nothing). But I don't see an obvious syntax error. — Gordon Linoff
– Gordon Linoff, Commented Jul 20, 2016 at 21:47
It is syntax error. Scalar subquery block does not have ORDER BY in its syntax. — Husqvik
– Husqvik, Commented Jul 20, 2016 at 21:56

joshweir · Accepted Answer · 2016-07-20 22:15:29Z

2

Do it like this:

UPDATE table
SET doc = (
 select r.myval
 from (
 SELECT 'table-2844-doc' || SUBSTR(doc_file, INSTR(doc_file, '.', -1)) myval, ROWNUM RN
 FROM docvers
 WHERE docvers.table_name = 'other_table'
 AND docvers.field_name = 'doc'
 ORDER BY VERSION DESC
 ) r
 where r.RN = 1
)
WHERE table_id = 2844

Select the data set first including the ROWNUM, then select from that data set the first row.

answered Jul 20, 2016 at 22:15

joshweir

5,6853 gold badges43 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3341592 Over a year ago

Weird that we have to make so many contorsions to be able to run a quite simple query. However, your solution stays quite clear, and does work. Thanks!

Collectives™ on Stack Overflow

Oracle SQL syntax error (missing right parenthesis)

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related