1

I am attempting to delete from a table in Oracle within a PL/SQL block statement. However, when i do so like this:

delete from Table a
    where rowid < (select max(rowid)
                    from Table b
                    where a.Number = b.Number AND a.Place = b.Place
                   );

(Where number is an integer and Place is a varchar and Table is a placeholder table name.)

I retrieve:

ORA-00928: missing SELECT keyword

I have seen only one question referring to this in a delete function and that is here. However, i struggled to fit this to my specification.

Any help would be appreciated. Thank you.

Kleo G
  • 247
  • 1
  • 4
  • 20
  • 5
    It's quite strange. Is 'Table' a just placeholder for real table name? I'd ask you to comment this delete statement and check again. If PL/SQL block works with commented statement and fails with uncommented - please publish exact text of the statement. – Sanders the Softwarer Dec 15 '17 at 13:10
  • Yes sorry, i should have made that clearer, Table is a placeholder table name. PL/SQL runs with this delete statement commented, however, uncommented it throws the ORA-00928, – Kleo G Dec 15 '17 at 14:08
  • 1
    Using dummy table (and column!) names is fine, but it helps it they're at least legal, to avoid confusion. Are the column names actually quoted identifiers, perhaps? Incidentally, if both are the same table, is this supposed to be deleting duplicates by keeping the row with the lowest rowid? If so it might take out more than you want - any row that doesn't have a duplicate will also be removed. – Alex Poole Dec 15 '17 at 14:16
  • You're correct! Ill modify the table name now. Sorry, instead of the in i was supposed to use less than, only due to the link i put in i thought that would help with the question i asked. – Kleo G Dec 15 '17 at 14:24
  • 2
    I don't know all there is to know about ROWIDs and I probably don't fully understand all that I think I know. But I'm positive they aren't sequential relative to a specific column. If you don't, at a minimum, account for your number column and place column in the outer WHERE clause you are going to remove a lot of rows you never meant to touch. – Stilgar Dec 15 '17 at 14:56
  • 1
    OK, but that's still not safe. "Table" isn't valid and neither is "Number". We aren't seeing exactly what you're running and we're left trying to guess what might be different for you - since what you're doing is, on the face of it, legal. I understand changing table/column names for posting, if it's done consistently and they're still representative; but if you can't demonstrate the problem with the dummy names we're still stuck. At the moment, for all we know, you retyped the whole thing and accidentally omitted whatever is actually wrong completely., and you just have a typo we can't see... – Alex Poole Dec 15 '17 at 14:58

0 Answers0