Home » SQL & PL/SQL » SQL & PL/SQL » delete (oracle 10g windows)
delete [message #317688] Fri, 02 May 2008 09:36 Go to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
how can i delete 3 records after each record. Find record from col1 value is delete_from_here then delete subsequent 3 records
eg:
col1	                col2
delete_from_here 	A
test1	                B
test2	                C
test3	                D
data1	                E
data2	                F
delete_from_here 	G
ABC1	                H
ABC2	                I
ABC3	                J
result1         	K
result2	                L

I WANT RESULT like this
COL1	               COL2
data1	                 E
data2	                F
result1	                K
result2	                L
Re: delete [message #317690 is a reply to message #317688] Fri, 02 May 2008 09:43 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW Posting Guidelines as stated in URL above

Rows in a table have NO inherent order.
this question is nonsensical.

[Updated on: Fri, 02 May 2008 10:00] by Moderator

Report message to a moderator

Re: delete [message #317694 is a reply to message #317690] Fri, 02 May 2008 10:37 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
why i this question nonsensical.
Re: delete [message #317708 is a reply to message #317688] Fri, 02 May 2008 12:42 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Can your table have a maximum 26 rows (A - Z)??? If so, then get the ascii equivalent of col2 and use a BETWEEN clause.

If you can have more than 26 rows, how would you define "subsequent rows?"
Re: delete [message #317751 is a reply to message #317708] Sat, 03 May 2008 01:26 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
HI
I need to find col1 value delete_from_here delete the rows from col1 delete_from_here till next 3 rows same way where every i find delete_from_here data in col1 from there next three rows
in
eg:
delete from table 
where col1='delete_from_here' 
next three rows

[Updated on: Sat, 03 May 2008 02:00]

Report message to a moderator

Re: delete [message #317755 is a reply to message #317688] Sat, 03 May 2008 03:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's a clue.
SQL> with 
  2    orig as (
  3      select 'delete_from_here' col1,'A' col2 from dual union all
  4      select 'test1','B' from dual union all
  5      select 'test2','C' from dual union all
  6      select 'test3','D' from dual union all
  7      select 'data1','E' from dual union all
  8      select 'data2','F' from dual union all
  9      select 'delete_from_here','G' from dual union all
 10      select 'ABC1','H' from dual union all
 11      select 'ABC2','I' from dual union all
 12      select 'ABC3','J' from dual union all
 13      select 'result1','K' from dual union all
 14      select 'result2','L' from dual
 15    ),
 16    data as (
 17      select col1, col2, 
 18             row_number () over (order by col2) rn,
 19             case 
 20               when col1='delete_from_here' 
 21               then row_number () over (order by col2) 
 22             end rn2
 23      from orig
 24    )
 25  select col1, col2, rn-max(rn2) over (order by col2) dist from data
 26  /
COL1             C       DIST
---------------- - ----------
delete_from_here A          0
test1            B          1
test2            C          2
test3            D          3
data1            E          4
data2            F          5
delete_from_here G          0
ABC1             H          1
ABC2             I          2
ABC3             J          3
result1          K          4
result2          L          5

12 rows selected.

Please next time post the test case: create table and insert statements.

Regards
Michel
Re: delete [message #317761 is a reply to message #317755] Sat, 03 May 2008 04:03 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
this the sample data
i need to find 'delete_from_here' in col1 and delete next 3 rows there are thousands of records
create table delete_next
(col1 varchar2(25));
truncate table delete_next
insert into delete_next
values('delete_from_here');
insert into delete_next
values('dssad');
insert into delete_next
values('sdadsa');
insert into delete_next
values('gfsdgsd');
insert into delete_next
values('data1');
insert into delete_next
values('data2');
insert into delete_next
values('delete_from_here');
insert into delete_next
values('rewre');
insert into delete_next
values('fdffasd');
insert into delete_next
values('hehwt');
insert into delete_next
values('result1');
insert into delete_next
values('result2');
commit


output should be
COL1
data1
data2
result1
result2
Re: delete [message #317767 is a reply to message #317761] Sat, 03 May 2008 04:34 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If your table really looks like this, you're out of luck. There's no way to determine order of records in a relational database table, unless there's another column which would make ordering possible. It might be a sequence, timestamp or something else, but it must exist so that ORDER BY clause returns records in desired order.

Example you created us useless; although it is obvious to a human, Oracle can't do that. Once again: imagine that a table is a basket full of apples. Which one of them is the first? Which one is the second?
Re: delete [message #317769 is a reply to message #317767] Sat, 03 May 2008 04:50 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
cannot we take only the value 'delete_from_here' in col1
and search for next three rows after each 'delete_from_here' in col1 and delete those subsequent rows three rows

Re: delete [message #317770 is a reply to message #317769] Sat, 03 May 2008 05:41 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
while attempting next three,

if values comes 'delete from' in 2nd row what could be logic

you suggest

my opinion...

it can be achieved through writing procedure/cursor or useing
arrays

let me also wait for some other sql select solution...

yours
dr.s.raghunathan
Re: delete [message #317773 is a reply to message #317770] Sat, 03 May 2008 06:27 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
we can use any type of operation like procedure ,cursor etc..
delete irrespective of value coming for next 3 rows
just find value and delete next 3 rows
Re: delete [message #317775 is a reply to message #317769] Sat, 03 May 2008 06:29 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
oracle_coorgi wrote on Sat, 03 May 2008 11:50
hi
cannot we take only the value 'delete_from_here' in col1
and search for next three rows after each 'delete_from_here' in col1 and delete those subsequent rows three rows



There is no "next three rows" if you do not specify an order by. It might even that the records with the value 'delete from here' are returned as the last rows of the resultset one day.
Re: delete [message #317778 is a reply to message #317775] Sat, 03 May 2008 06:36 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
see i need to point the cursor where the value in col1 is 'delete_from_here' then delete subsequent 3 rows
Re: delete [message #317782 is a reply to message #317778] Sat, 03 May 2008 09:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Question Is this not what I gave you??? Question

As others said, if you have not a column to order, there is no "subsequent rows". Or if you want change "order by col2" by "order by null" in my previous query.

Regards
Michel

[Updated on: Sat, 03 May 2008 09:16]

Report message to a moderator

Re: delete [message #318095 is a reply to message #317778] Mon, 05 May 2008 09:58 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
oracle_coorgi wrote on Sat, 03 May 2008 07:36
see i need to point the cursor where the value in col1 is 'delete_from_here' then delete subsequent 3 rows



You're just not getting it, Mr. Stubborn. there is no such thing as "subsequent 3 rows." Maybe if every person on this forum tells you you still won't understand, but I'll do my part and tell you also.
Re: delete [message #318310 is a reply to message #317688] Tue, 06 May 2008 08:19 Go to previous messageGo to next message
drewsmith70
Messages: 22
Registered: April 2008
Location: New Hampshire
Junior Member
If you can't get your head around the fact that your data isn't ordered - that the order returned by your "SELECT" is unpredictable - then select your data into a collection and go from there.
Re: delete [message #318314 is a reply to message #318310] Tue, 06 May 2008 08:26 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Of course, the order that it is selected into the collection will still be indeterminate.... Cool

In case the OP wants proof that we're not making it all up, here it is:
Quote:
Use the ORDER BY clause to order rows returned by the statement. Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.
Previous Topic: Encryption of data in a column
Next Topic: finding size of remote blobs
Goto Forum:
  


Current Time: Thu Dec 08 16:36:55 CST 2016

Total time taken to generate the page: 0.05130 seconds