Delete n rows of a table

Question

Delete n rows of a table

Navigation

#1 by (2 votes)
#2 by (0 votes)

1

My question is this:

How can I delete n rows from a table if they meet the condition x rows in total? Where x> > > > > n. I want to use it because I am going to have a job that runs at a certain time and if every time it is executed it will delete 100000000000 rows, it can slow down the DB and I want to set a limit of deletions per iteration.

This delete would be fulfilled by millions of records and I just want to delete an amount x. How do I do it?

DELETE trazabilidad_pedro 
WHERE cd_intercambio = rec_mon_borrado.cd_intercambio 
AND cd_estado NOT IN ('06','07','08','09','99');'

sql plsql

asked by user81275 27.03.2018 в 13:53

source

2 answers

Error in the function Walker_Nav_Menu wordpress How can I replace the image?

score 2 · Answer 1

The closest thing you can do is use limit :

DELETE trazabilidad_pedro 
WHERE cd_intercambio = rec_mon_borrado.cd_intercambio 
AND cd_estado NOT IN ('06','07','08','09','99') LIMIT 1000

This would eliminate the first 1000 records that meet that condition.

score 0 · Answer 2

If it is Oracle, as you indicated with the plsql tag, one way to solve it is to use the ROWID in the following way, for example to delete 1000 records:

DELETE FROM trazabilidad_pedro 
    WHERE ROWID IN (SELECT ROWID
                            FROM (SELECT ROWID, 
                                         ROW_NUMBER() OVER(ORDER BY ROWID) rn
                                         FROM trazabilidad_pedro 
                                  )
                            WHERE rn <= 1000 
           )