CODEWITHSUNDEEP
sqloracle-database
salazar44
salazar44

Reputation: 142

Update Oracle Table using rownum and order by

Im trying to update a field in an oracle table, but I would only like to update 75 rows based on the order by creationdate field ascending.

This is what I have so far, but its not working.

UPDATE extractcandidate
SET process = 15
WHERE process IN
(Select process from extractcandidate where process = 1500 and rownum <=75 order by creationdate);

Upvotes: 1

Views: 5377

Answers (2)

wolφi
wolφi

Reputation: 8361

As @Gordon mentioned, you need to do the ORDER BY before the ROWNUM.

Firstly, order by creationdate:

SELECT *
  FROM extractcandidate
 WHERE process=1500
 ORDER BY creationdate;

Secondly, limit the number to 75:

SELECT * 
  FROM (
        SELECT *
          FROM extractcandidate
         WHERE process=1500
         ORDER BY creationdate
       ) 
  WHERE rownum <= 75;

Now you can feed it into the UPDATE. To find the correct rows, you would normally use a primary key column. This seems to be missing in your case, so you can fall back to Oracle's internal ROWID:

UPDATE extractcandidate 
   SET process=15
 WHERE rowid IN (
                 SELECT ri 
                   FROM (
                         SELECT rowid as ri
                           FROM extractcandidate
                          WHERE process=1500
                          ORDER BY creationdate
                         ) 
                  WHERE rownum <= 75
                 );

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You need an additional subquery for your method to work:

UPDATE extractcandidate
    SET process = 15
    WHERE ec.process = 1500 AND
          creationdate IN (SELECT ec.creationdate
                           FROM (SELECT ec.*
                                 FROM extractcandidate ec 
                                 WHERE ec.process = 1500 
                                 ORDER BY ec.creationdate
                                )
                           WHERE rownum <= 75
                          );

Notes:

  • You need to do the sorting before using rownum.
  • The comparison needs to be on creation date, not process.

Upvotes: 0

Related Questions