3

I'm trying to UPDATE values in an Oracle database table (of salary information) that has no row IDs or a primary key. I'm writing these statements in LinqPad.

This syntax should work just fine in SQL Server SQL:

UPDATE schedule_amounts 
SET ANNUAL_RATE = 44000
WHERE SCHEDULE_ID = 'LCSD'
  and SCHEDULE_NO = 2014
  and SCHEDULE_LEVEL = 100
  and SCHEDULE_STEP = 17

SELECT * FROM schedule_amounts

But the driver LinqPad uses is returning ORA-00933: SQL command not properly ended.

Using multiple WHERE conditions is the only way I can specify the exact row I need to update. I have Googled Oracle's UPDATE syntax but I just can't seem to find an easy-to-understand example of how to do what, to me, is rather simple in SQL Server.

10
  • 9
    Oracle doesn't like it Do you get an error? Can you elaborate on that statement? Can you post the create table for schedule_amounts? That query should work - see this demo -- sqlfiddle.com/#!4/305d3/1
    – Taryn
    May 15, 2013 at 0:29
  • 2
    In addition to bluefeet's valuable comment, I would start thinking about adding a PK to this table. Can save lots of headaches later. May 15, 2013 at 6:31
  • OK All, this was my bad. It has to do with the query tool I was using (Linqpad). When connected to Oracle, it doesn't behave like it does for SQL Server connections. I can run multiple SQL commands all at the same time, i.e. I can run an UPDATE and a SELECT at the same time to view the results of what I'm updating. But the Oracle driver I was using with LinqPad didn't like the multiple commands. Sorry, all. Should I delete this question?
    – Baodad
    May 15, 2013 at 18:17
  • 7
    Deleting your own question will count against you to some degree. I think the question can be salvaged, if you explain that you were using LinqPad, remove the "doesn't like it" bit and list the actual error message, verbatim, and then post an answer describing exactly how you fixed or worked around the issue. A future user may come across the same issue in LinqPad. May 15, 2013 at 20:31
  • 2
    I edited the question with the exact error message. I would post my answer but the question has been closed. I did try terminating my statement with a ; but that didn't work. I definitely think GO; between the UPDATE and the SELECT statements would have worked. Cheers!
    – Baodad
    May 17, 2013 at 3:52

1 Answer 1

2

Thank you all for your comments. You deserve the credit for this answer. To summarize:

The Oracle driver for LinqPad that I'm using (dotConnect Direct Mode based on OCI 8) wants the statement to be formatted like this (yes, wihout any semicolons):

UPDATE schedule_amounts 
SET ANNUAL_RATE = 44000
WHERE SCHEDULE_ID = 'LCSD'
    and SCHEDULE_NO = 2014
    and SCHEDULE_LEVEL = 100
    and SCHEDULE_STEP = 17
GO
SELECT * FROM schedule_amounts
1
  • In particular, for multiple statements, you need to replace any ; (semicolons) with a newline and GO
    – llaughlin
    May 30, 2018 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.