a sql query from oracle table

Question

I have only one column roll number in my oracle table. Lots of data are there in one column.

   roll number  

  -------------

      1 
      2
      3
      4
      5
      .
      .
      .

When i want to delete some roll numbers out of this table then i am writing:

delete from table name where roll number='2'; // 1 row deleted.

if i want to delete 5 from table then

delete from table name where roll number='5'; // 1 row deleted.   

Similarly when i want to delete 100 records like this then i have to replace roll number field and it is quite time taking. Is there any quick method how can i delete selective rows from the table?

Answer 1

If the ids to delete are consecutive you could use BETWEEN:

DELETE FROM your_table WHERE roll_number BETWEEN 5 AND 104

If not, you could use IN:

DELETE FROM your_table WHERE roll_number IN (5, 9, 110, ... )

Answer 2

If u already know the range u can use Between else if u know the total numbers to be deleted then u can use IN like....

delete from table where roll number BETWEEN ....;

Answer 3

You can get the list of number from another query and put it in a IN condition, so for example :

delete from table name where roll number IN (select roll number from table WHERE somecondition);