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?

Answer 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