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

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?

CodePudding user response：

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

CodePudding user response：

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.