Can't update only 1 row. How to change 1 row?

Question

I found out that Oracle does not support LIMIT clause unlike to MySQL and to update only 1 row in table i've tried this:

UPDATE "Schedule" 
   SET "Position" = 'Manager' 
 WHERE "Position" IN 
       (SELECT "Position" 
          FROM "Schedule" 
         WHERE "Position"='Tester' 
         OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY);

And 3 rows are updated.

When i run this:

SELECT "Position" 
  FROM "Schedule" 
 WHERE "Position"='Tester' 
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;

i get only one row (as it should be).

But updating 1 row doesn't work as i mentioned before. Are there any other methods to update only 1 specific row?

Reference: https://stackoverflow.com/questions/40928825/sql-command-not-properly-ended-when-using-limit — bmfdds, Nov 28 '21 at 11:00

Barbaros Özhan · Accepted Answer · 2021-11-28T13:20:00.203

1

You need just a slight, provided that your DB's version is at least 12c, change by replacing the part WHERE Position IN (SELECT Position with WHERE ID IN (SELECT ID as having a primary column. The current case has no sense, since all testers will be converted to managers even replacing the IN <subquery> with ='Tester'. So use the following update statement by removing double quotes

UPDATE Schedule
   SET Position = 'Manager'
 WHERE ID IN
       (SELECT ID
          FROM Schedule
         WHERE Position = 'Tester'
         FETCH NEXT 1 ROW ONLY);

Demo

edited Nov 28 '21 at 13:20

answered Nov 28 '21 at 11:30

Barbaros Özhan

59,113
10
31
55

i get this error.. ORA-00904: "ID": invalid identifier – bmfdds Nov 28 '21 at 11:51
What's your DB's version? @a_needs_help – Barbaros Özhan Nov 28 '21 at 11:52
version is this (( 19c – bmfdds Nov 28 '21 at 12:16
did you remove the double-quotes @a_needs_help ? Btw, do you have a primary key column ? – Barbaros Özhan Nov 28 '21 at 12:17
1

thanks a lot, i found where the problem was :) – bmfdds Nov 28 '21 at 19:13
Can you help, please, with taking 3 last rows of table? i've tried TOP(3) or using variable of num of rows but it didn't work out – bmfdds Nov 28 '21 at 19:20
@a_needs_help you can pick the last three one while sorting descendingly such as `ORDER BY ID DESC FETCH NEXT 3 ROWS ONLY` – Barbaros Özhan Nov 28 '21 at 19:23
my task was to order ascendingly by salary and take 3 rows with largest salaries – bmfdds Nov 28 '21 at 19:38
@a_needs_help Can you share your sample data and table structure within a demo as stated within the answer ? – Barbaros Özhan Nov 28 '21 at 19:58
WITH SRC AS ( SELECT TOP (3) Num FROM Schedule ORDER BY Salary ASC ) SELECT * FROM SRC – bmfdds Nov 29 '21 at 12:28

Bob Jarvis - Слава Україні · Answer 2 · 2021-11-28T14:21:12.233

Ideally your "Schedule" table would have a primary key and you'd use that to perform your update. Assuming that "Schedule" doesn't have a primary key you can use ROWID:

UPDATE "Schedule" 
  SET "Position" = 'Manager' 
  WHERE ROWID IN (SELECT ROWID
                    FROM "Schedule" 
                    WHERE "Position" = 'Tester' 
                    OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY);

Note that using ROWID like this is very poor practice, and all tables should have a primary key specified when they're created so that individual rows can be found reliably. For a column or set of columns to be a valid primary key they must be A) non-NULL, B) unique, and C) unchanging.

Also - I strongly suggest that you get in the habit of using names for tables and columns which do not require that they be quoted. Quoted identifiers are an opportunity for syntax errors that do nothing to make your life better. If you create a table named SCHEDULE you can still refer to is as Schedule, ScHeDuLe, schedule, or whatever other combination of upper and lower case letters you care to use in your code.

Can't update only 1 row. How to change 1 row?

2 Answers2