0

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?

Community
  • 1
  • 1
user3341592
  • 1,419
  • 1
  • 17
  • 36
  • 1
    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 Jul 20 '16 at 21:47
  • 2
    It is syntax error. Scalar subquery block does not have ORDER BY in its syntax. – Husqvik Jul 20 '16 at 21:56

1 Answers1

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.

joshweir
  • 5,427
  • 3
  • 39
  • 59
  • 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! – user3341592 Jul 25 '16 at 08:34