0
UPDATE AC01_FILE_BLOB
SET AC01_FILE_BLOB.DATA_CONTROLLO=(TO_DATE(SELECT TO_CHAR(SYSDATE, 
                                                  'yyyy/mm/dd hh24:mi:ss') 
                                              FROM DUAL))
WHERE AC01_FILE_BLOB.DATA_CONTROLLO = (SELECT DATA_CONTROLLO 
                                         FROM AC01_FILE_BLOB 
                                        WHERE ROWNUM = 1 
                                     ORDER BY DATA_CONTROLLO desc)

[Error Code: 936, SQL State: 42000] ORA-00936

massko
  • 589
  • 1
  • 7
  • 22
Dario
  • 9
  • 1
  • 5
  • The error code appears to mean missing right parenthesis, but since I don't see that in the query you posted I would assume that either a) you didn't post the actual query which caused this error, or b) something in your query is so wrong that Oracle is rolling over and dying manifesting in this error. – Tim Biegeleisen Nov 16 '16 at 14:25
  • Remove `order by` clause from sub-query in the `where` clause it's not allowed in the top-level sub-query. – Nick Krasnov Nov 16 '16 at 14:32
  • 1
    @Dario there is much in your update statement that doesn't make sense. First off: why select sysdate from dual when you could simply use sysdate? e.g. `set data_controllo = to_char(sysdate, 'mm-dd-yyyy hh24:mi:ss')`. Next, why are you storing different things in the DATA_CONTROLLO field? HASH_FILE seems a very different thing than a date-as-a-string. – Boneist Nov 16 '16 at 14:33
  • 1
    @Dario Finally, if you're trying to get the hash_file relating to the field with the "highest" data_controllo value, you're going about it the wrong way. You need to [order first and then restrict on rownum](http://stackoverflow.com/questions/10430762/using-rownum-in-oracle-sql-statement-in-combination-with-order-by-clause). Or you could use the row_number() analytic function, or you could even use the keep dense rank first aggregate function. – Boneist Nov 16 '16 at 14:34
  • And your subquery in where condition does not return newest record. `rownum` is applied before order by. – Kacper Nov 16 '16 at 14:39
  • I edited the post because the query was written with some wrong call f columns, now there's the right columns, as u all can see, i need to UPDATE the value of DATA_CONTROLLO (who's a date) with the SYSDATE, who was actually converted in DATE value – Dario Nov 16 '16 at 15:05
  • So you're taking something that's a date, converting it to a string, and then converting it back into a date (except without specifying the date format)? If you're wanting to strip out the time elements of sysdate, why not just do `trunc(sysdate)`? – Boneist Nov 16 '16 at 15:34

1 Answers1

1

That's the solution:

 UPDATE AC01_FILE_BLOB
 SET AC01_FILE_BLOB.DATA_CONTROLLO = (SYSDATE)
 WHERE AC01_FILE_BLOB.DATA_CONTROLLO = (SELECT max(DATA_CONTROLLO)
 FROM AC01_FILE_BLOB where ROWNUM = 1 );

The problem was about the SYSDATE value, when i have to INSERT a record, i've to specify the date format plus the value, instead, in the SYSDATE i dont have to. Plus the "order by" command was useless cause i must use max(DATA_CONTROLLO), and that's the problem [SOLVED] about i had to be sure about the date updated was the MAX one, thank you all!

Dario
  • 9
  • 1
  • 5
  • You don't need the brackets around sysdate, nor do you need the `rownum = 1` in your subquery; you'll only ever get one row back when you do an aggregate function over the entire table. – Boneist Nov 16 '16 at 15:35
  • So i should change the: FROM AC01_FILE_BLOB where ROWNUM = 1 ); in FROM AC01_FILE_BLOB; ? Did i understood correctly? – Dario Nov 16 '16 at 15:40
  • you could just do: `update ac01_file_blob set data_controllo = sysdate where data_controllo = (select max(data_controllo) from ac01_file_blob);` – Boneist Nov 16 '16 at 15:42