Home » SQL & PL/SQL » SQL & PL/SQL » Delete even rows of a table
Delete even rows of a table [message #199567] Wed, 25 October 2006 02:10 Go to next message
mr_deb
Messages: 3
Registered: October 2006
Junior Member
hi

Can anyone tell me how to delete even rows of a table or rather alternate rows of a table.

Waiting for your reply.Thanx in advance.
Re: Delete even rows of a table [message #199574 is a reply to message #199567] Wed, 25 October 2006 02:37 Go to previous messageGo to next message
mahendramahendra
Messages: 6
Registered: October 2006
Junior Member
delete from table_name where rowid in (
select rid from
( select rownum cnt,rowid rid from table_name where rownum < 7)
where mod(cnt,2) = 0 )

[Updated on: Wed, 25 October 2006 02:37]

Report message to a moderator

Re: Delete even rows of a table [message #199576 is a reply to message #199574] Wed, 25 October 2006 02:51 Go to previous messageGo to next message
mr_deb
Messages: 3
Registered: October 2006
Junior Member
Can u plz explain how its working
Re: Delete even rows of a table [message #199607 is a reply to message #199576] Wed, 25 October 2006 04:20 Go to previous messageGo to next message
Littlefoot
Messages: 19688
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
ROWNUM is a pseudocolumn which shows row number of the fetched record. MOD(rownum, 2) is equal to 0 when rownum is an even number (and is equal to 1 when rownum is an odd number). So, it deletes all records where ROWNUM is even.
SQL> select rownum, mod(rownum, 2) mod_2, ename
  2 from emp
SQL> /

    ROWNUM      MOD_2 ENAME
---------- ---------- ----------
         1          1 SMITH
         2          0 ALLEN
         3          1 BURGLAR
         4          0 JONES
         5          1 TIGER
         6          0 BLAKE
         7          1 CLARK
         8          0 SCOTT
         9          1 KING
        10          0 TURNER
        11          1 ADAMS
        12          0 JAMES
        13          1 FORD
        14          0 MILLER


[EDIT] Added a screen output

[Updated on: Wed, 25 October 2006 04:23]

Report message to a moderator

Re: Delete even rows of a table [message #199624 is a reply to message #199607] Wed, 25 October 2006 04:57 Go to previous messageGo to next message
mr_deb
Messages: 3
Registered: October 2006
Junior Member
i got the answer sir.Thanx very much
Re: Delete even rows of a table [message #199639 is a reply to message #199624] Wed, 25 October 2006 10:02 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Can't help but asking: what on earth can be the functional requirement to remove even rows?

(please mind that the order in which Oracle saves rows in a table is not known or effected by developers, users or anyone).

Regards,
Sabine

[Updated on: Wed, 25 October 2006 10:03]

Report message to a moderator

Re: Delete even rows of a table [message #199642 is a reply to message #199639] Wed, 25 October 2006 10:06 Go to previous messageGo to next message
Littlefoot
Messages: 19688
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Who can tell? This might be another way to delete half of all records stored in a table.
Re: Delete even rows of a table [message #199643 is a reply to message #199642] Wed, 25 October 2006 10:13 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Maybe a new contest: find ways to randomly delete half of the records in a table Laughing
Re: Delete even rows of a table [message #199668 is a reply to message #199643] Wed, 25 October 2006 12:42 Go to previous messageGo to next message
biohazardbill
Messages: 57
Registered: March 2006
Member
Maybe records were double entered and he is sorting somehow in an effort to remove dupes.
Re: Delete even rows of a table [message #199673 is a reply to message #199668] Wed, 25 October 2006 13:34 Go to previous messageGo to next message
joy_division
Messages: 4528
Registered: February 2005
Location: East Coast USA
Senior Member
Nah, this is definitely an interview question (or homework). If he really wanted to delete duplicates, there are so much more accurate ways to do it.
Re: Delete even rows of a table [message #549203 is a reply to message #199567] Wed, 28 March 2012 16:30 Go to previous messageGo to next message
muchObliged
Messages: 3
Registered: March 2012
Location: Iowa
Junior Member
I had the same question. My use for it is to delete every other record in a grouped signon table because a stored proc created duplicate signon credentials. I like the solution given!
Re: Delete even rows of a table [message #549206 is a reply to message #549203] Wed, 28 March 2012 17:05 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
rows in a table are like balls in a basket & have NO inherent order.

which balls in the basket are even & which are odd?
Re: Delete even rows of a table [message #549208 is a reply to message #549203] Wed, 28 March 2012 17:15 Go to previous messageGo to next message
cookiemonster
Messages: 11070
Registered: September 2008
Location: Rainy Manchester
Senior Member
muchObliged wrote on Wed, 28 March 2012 22:30
I had the same question. My use for it is to delete every other record in a grouped signon table because a stored proc created duplicate signon credentials. I like the solution given!

Why? If you have duplicates then surely you need a delete that will remove the duplicates.
Re: Delete even rows of a table [message #549214 is a reply to message #549208] Wed, 28 March 2012 17:55 Go to previous messageGo to next message
muchObliged
Messages: 3
Registered: March 2012
Location: Iowa
Junior Member
Grouped by primary keys, worked like a charm
Re: Delete even rows of a table [message #549216 is a reply to message #549214] Wed, 28 March 2012 18:00 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Delete even rows of a table [message #549217 is a reply to message #549216] Wed, 28 March 2012 18:58 Go to previous messageGo to next message
muchObliged
Messages: 3
Registered: March 2012
Location: Iowa
Junior Member
I was answering your question...
Re: Delete even rows of a table [message #549218 is a reply to message #549217] Wed, 28 March 2012 19:19 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
The Posting Guidelines state,
"If you found an answer yourself, post it. That way we know the issue is resolved and we might learn from it."

Simply posting it works or it does not work is 100% useless noise.
Re: Delete even rows of a table [message #575545 is a reply to message #199567] Wed, 23 January 2013 23:58 Go to previous messageGo to next message
MAHESH1244
Messages: 3
Registered: January 2013
Location: hyd
Junior Member
WITH TEMP AS
(
SELECT ROW_NUMBER() OVER (ORDER BY EMPID) AS ROWNUM,* FROM EMPLOYEE
)DELETE FROM TEMP WHERE ROWNUM%2=0
Re: Delete even rows of a table [message #575547 is a reply to message #575545] Thu, 24 January 2013 00:32 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

This Code can be rewriten as

DELETE FROM EMPLOYEE WHERE EMPID IN
  (SELECT EMPID
  FROM
    (SELECT ROW_NUMBER() OVER (ORDER BY EMPID) AS RN, EMPID FROM EMPLOYEE E
    )
  WHERE mod(rn,2)=0
  );


Regards,
Nathan
Re: Delete even rows of a table [message #575551 is a reply to message #575545] Thu, 24 January 2013 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
MAHESH1244 wrote on Thu, 24 January 2013 06:58
WITH TEMP AS
(
SELECT ROW_NUMBER() OVER (ORDER BY EMPID) AS ROWNUM,* FROM EMPLOYEE
)DELETE FROM TEMP WHERE ROWNUM%2=0


SQL> WITH TEMP AS
  2  (
  3  SELECT ROW_NUMBER() OVER (ORDER BY EMPID) AS ROWNUM,* FROM EMPLOYEE
  4  )DELETE FROM TEMP WHERE ROWNUM%2=0
  5  /
SELECT ROW_NUMBER() OVER (ORDER BY EMPID) AS ROWNUM,* FROM EMPLOYEE
                                             *
ERROR at line 3:
ORA-00923: FROM keyword not found where expected


Please TEST your query before posting silly things.
Even if you remove the reserved word it is wrong:
SQL> WITH TEMP AS
  2  (
  3  SELECT ROW_NUMBER() OVER (ORDER BY EMPID) AS R,* FROM EMPLOYEE
  4  )DELETE FROM TEMP WHERE R%2=0
  5  /
SELECT ROW_NUMBER() OVER (ORDER BY EMPID) AS R,* FROM EMPLOYEE
                                               *
ERROR at line 3:
ORA-00936: missing expression

Even if you fix the error it is wrong:
SQL> WITH TEMP AS
  2  (
  3  SELECT ROW_NUMBER() OVER (ORDER BY EMPID) AS R,employee.* FROM EMPLOYEE
  4  )DELETE FROM TEMP WHERE R%2=0
  5  /
)DELETE FROM TEMP WHERE R%2=0
 *
ERROR at line 4:
ORA-00928: missing SELECT keyword

And so on.

Regards
Michel
Re: Delete even rows of a table [message #575552 is a reply to message #575547] Thu, 24 January 2013 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sss111ind wrote on Thu, 24 January 2013 07:32
This Code can be rewriten as


There is no need of analytic function as it has been showed above many years ago.

Regards
Michel

Re: Delete even rows of a table [message #575581 is a reply to message #199643] Thu, 24 January 2013 07:58 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
skooman wrote on Wed, 25 October 2006 11:13
Maybe a new contest: find ways to randomly delete half of the records in a table


delete your_table
  where rowid in (
                  with t as (
                             select  rowid rid,
                                     ceil(count(*) over() / 2) half_cnt,
                                     row_number() over(order by dbms_random.value) rn
                               from  your_table
                            )
                  select  rid
                    from  t
                    where rn <= half_cnt
                 )
/


SY.

[Updated on: Thu, 24 January 2013 08:00]

Report message to a moderator

Re: Delete even rows of a table [message #575586 is a reply to message #575581] Thu, 24 January 2013 08:44 Go to previous messageGo to next message
_jum
Messages: 490
Registered: February 2008
Senior Member
ok here is my version:
DELETE your_table
 WHERE rowid in 
 (SELECT rid 
    FROM
    (SELECT rowid rid, ntile (2) over (order by 1) tile  
       FROM your_table) t2
   WHERE tile=2  
 );


ADD: Experts would use
(SELECT rowid rid, ntile (2) over (order by mod(rownum+1,2)) tile

to DELETE only the EVEN rownum Cool

[Updated on: Thu, 24 January 2013 09:07]

Report message to a moderator

Re: Delete even rows of a table [message #575589 is a reply to message #575586] Thu, 24 January 2013 09:10 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
_jum wrote on Thu, 24 January 2013 09:44
ok here is my version:


No good (in my opinion). Order by 1 will take rows in order they are fetched, assign 1 to each row and sort by 1. As a result row order will remain same as fetch order. In other word, your code will delete second half of the table ordered by rowid:

SQL> select  *
  2    from  emp1
  3  /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> delete emp1
  2   WHERE rowid in 
  3   (SELECT rid 
  4      FROM
  5      (SELECT rowid rid, ntile (2) over (order by 1) tile  
  6         FROM emp1) t2
  7    WHERE tile=2  
  8   );

7 rows deleted.

SQL> select  *
  2    from  emp1
  3  /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

7 rows selected.

SQL> 


And a better illustration comparing two deletes:

SQL> drop table emp1 purge;

Table dropped.

SQL> create table emp1
  2  as select * from emp
  3  order by ename
  4  /

Table created.

SQL> select * from emp1;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

14 rows selected.

SQL> delete emp1
  2   WHERE rowid in 
  3   (SELECT rid 
  4      FROM
  5      (SELECT rowid rid, ntile (2) over (order by 1) tile  
  6         FROM emp1) t2
  7    WHERE tile=2  
  8   );

7 rows deleted.

SQL> select * from emp1;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20

7 rows selected.

SQL> rollback;

Rollback complete.

SQL> delete emp1
  2    where rowid in (
  3                    with t as (
  4                               select  rowid rid,
  5                                       ceil(count(*) over() / 2) half_cnt,
  6                                       row_number() over(order by dbms_random.value) rn
  7                                 from  emp1
  8                              )
  9                    select  rid
 10                      from  t
 11                      where rn <= half_cnt
 12                   )
 13  /

7 rows deleted.

SQL> select * from emp1;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

7 rows selected.

SQL> 


SY.
Re: Delete even rows of a table [message #575593 is a reply to message #575589] Thu, 24 January 2013 09:35 Go to previous messageGo to next message
_jum
Messages: 490
Registered: February 2008
Senior Member
@SY - Thanks - you are definitely an expert and would use the (later added) expert version with:
    (SELECT rowid rid, ntile (2) over (order by mod(rownum+1,2)) tile
Re: Delete even rows of a table [message #575594 is a reply to message #575593] Thu, 24 January 2013 09:40 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
Table will ALWAYS have rows with "even numbers"; except when table contains only ONE row!
Re: Delete even rows of a table [message #575606 is a reply to message #575593] Thu, 24 January 2013 11:23 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
Why do you think using order by mod(rownum+1,2) makes it random? It will delete every other fetched row. So if you issue delete, rollback and delete again you will see that it deletes same rows each time. Using order by dbms_random.value ensures (with certain degree of probability) different set of rows is deleted each time.

SY.
Re: Delete even rows of a table [message #575635 is a reply to message #575551] Thu, 24 January 2013 23:53 Go to previous messageGo to next message
MAHESH1244
Messages: 3
Registered: January 2013
Location: hyd
Junior Member
WITH TEMP AS
(
SELECT ROW_NUMBER() OVER (ORDER BY EMPID) AS ROWNUM,* FROM EMP
)
DELETE FROM TEMP WHERE ROWNUM%2=0



1)SELECT ROW_NUMBER() OVER (ORDER BY EMPID) AS ROWNUM,* FROM EMP
output:
1 100 MAHESH 10 3 20000 2013-01-22
2 104 JILANI 10 2 4000 1981-01-30
3 108 JAYA 10 1 20000 1980-01-05
4 112 MAHESH 10 3 23030 1990-02-08
5 114 MAHESH 10 3 23030 1990-02-08




2)WITH TEMP AS
(
SELECT ROW_NUMBER() OVER (ORDER BY EMPID) AS ROWNUM,* FROM EMP
)
DELETE FROM TEMP WHERE ROWNUM%2=0
output will be in as

(2 row(s) affected)


3)select * from EMP
output:
100 MAHESH 10 3 20000 2013-01-22
108 JAYA 10 1 20000 1980-01-05
114 MAHESH 10 3 23030 1990-02-08

4)SELECT ROW_NUMBER() OVER (ORDER BY EMPID) AS ROWNUM,* FROM EMP
output:
1 100 MAHESH 10 3 20000 2013-01-22
2 108 JAYA 10 1 20000 1980-01-05
3 114 MAHESH 10 3 23030 1990-02-08




here is my ans ....y you are getting error i don't know





Re: Delete even rows of a table [message #575636 is a reply to message #575635] Thu, 24 January 2013 23:59 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

Mahes1244,

What tool are You using and what is Oracle version.(I think It is not Oracle).
Because Oracle never support like
select 89%2 from dual;


Regards,
Nathan
Re: Delete even rows of a table [message #575637 is a reply to message #575636] Fri, 25 January 2013 00:06 Go to previous messageGo to next message
MAHESH1244
Messages: 3
Registered: January 2013
Location: hyd
Junior Member
ITs sql server bro
Re: Delete even rows of a table [message #575638 is a reply to message #575637] Fri, 25 January 2013 00:52 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

It's Oracle Faqs and not Sqlserver faqs.
Re: Delete even rows of a table [message #575639 is a reply to message #575637] Fri, 25 January 2013 00:52 Go to previous message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And this is an Oracle site, so stay with Oracle solutions.

Also, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

Regards
Michel
Previous Topic: Export_Data_From_Table_To_Different_Excel
Next Topic: Table Partitioning
Goto Forum:
  


Current Time: Wed Oct 22 01:30:35 CDT 2014

Total time taken to generate the page: 0.08541 seconds