Oracle UPDATE followed by SELECT in LinqPad

oracleupdate

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.

Best Answer

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

Related Solutions

How to make this Select an Update

UPDATE pallet
SET    pallet_status = 2
WHERE  pallet.pallet_code in (SELECT Pallet_code 
                              FROM (
                                   SELECT p.Pallet_code,
                                          COUNT(ps.SOHP_ID) AS "Roll Count"
                                   FROM   PALLET p, PSOH ps
                                   WHERE  p.pallet_code = ps.SOHP_bin 
                                   AND    p.Plant_Code = 36 
                                   AND    p.Pallet_Status = 1 
                                   AND    p.pallet_CREA_TIME <= DATE '2017-4-11'
                                   GROUP BY   p.pallet_code
                                   HAVING     COUNT(ps.sohp_id) = 0
                                   ));

SQL Server – Best Way to Iterate and Update Column in Each Row

Your code won't work as is. You're missing a lot of WHILE loops. Also, your UPDATE query will update every row in the table. Not only will that take a long time, but you'd be left with wrong results at the end of it even if the rest of your code was fixed.

I'm not aware of any easy answers for updating a column in an unpartitioned heap with billions of rows, but my advice to you is as follows:

Test your code until you're confident that you have something that works.
Have your code process a single station. Picking the station with the most rows is a good choice.
Measure how long that code takes and multiply it by the number of stations. Is that within your maintenance window? If so, great, run the full code and be done with it.
If your code is too slow, measure each part to figure out where the bottleneck is, look at execution plans, and try to figure out where changes need to be made.

You'll get much better help if you can identify a specific part of the code that you need help with. Good luck.

Best Answer

Related Solutions

How to make this Select an Update

SQL Server – Best Way to Iterate and Update Column in Each Row

Related Question