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?

you can use OR in your delete statement. Can you explain what really you want to achieve? — Jainendra
– Jainendra, Commented Apr 28, 2012 at 9:42

Mark Byers · Accepted Answer · 2012-04-30 08:35:42Z

7

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, ... )

edited Apr 30, 2012 at 8:35

answered Apr 28, 2012 at 9:42

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thilo Over a year ago

+1. I'd lose the quotes if these are really numbers (I know the original question has them, too, but if the data really is in a text column, BETWEEN won't work so well).

aleroot · Accepted Answer · 2012-04-28 09:43:31Z

1

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);

answered Apr 28, 2012 at 9:43

aleroot

73k32 gold badges182 silver badges220 bronze badges

2 Comments

Mark Byers Over a year ago

+1 but your example is a bit strange. You could just do DELETE FROM table WHERE somecondition instead.

aleroot Over a year ago

Yes but maybe he could get the interested ids from another table, is only an example, i don't know the relations of the entire database ...

Addicted · Accepted Answer · 2012-04-28 09:47:08Z

0

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 ....;

answered Apr 28, 2012 at 9:47

Addicted

1,7042 gold badges16 silver badges24 bronze badges

Collectives™ on Stack Overflow

a sql query from oracle table

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related