Problem description:

there is a table which has range of numbers like this

TABLE_RANGE

TABLE_NAME | RANGE_START | RANGE_END

example 1000 2000
example 3000 5000
example2 5000 7000
example2 1000 3000
example2 8000 9000

Now i have create a new table by excluding these ranges of number from the table with their respective table names given.

we can write like this..

create table example_new as select * from example where number not between (select RANGE_START from TABLE_RANGE where TABLE_NAME='example') and (select RANGE_END from TABLE_RANGE where TABLE_NAME='example');

but this wont work as the sub query select RANGE_START from TABLE_RANGE where TABLE_NAME='example' gives both 1000 and 3000.

Is there any solution for this..

i want to delete numbers based on the tABLE_RANGE table

Any creative solution?
Dont go for scripting or PL/SQL..
Any idea in queries?

Please reply if your not clear with the question