Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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 –  bluefeet May 15 '13 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. –  dezso May 15 '13 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 '13 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. –  Aaron Bertrand May 15 '13 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 '13 at 3:52

1 Answer 1

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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