OraFAQ Forum: SQL & PL/SQL » Delete even rows of a table

Home » SQL & PL/SQL » SQL & PL/SQL » Delete even rows of a table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Delete even rows of a table [message #199567]

Wed, 25 October 2006 02:10

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.

Report message to a moderator

Re: Delete even rows of a table [message #199574 is a reply to message #199567]

Wed, 25 October 2006 02:37

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

mr_deb
Messages: 3
Registered: October 2006

Junior Member

Can u plz explain how its working

Report message to a moderator

Re: Delete even rows of a table [message #199607 is a reply to message #199576]

Wed, 25 October 2006 04:20

Littlefoot
Messages: 21824
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.

Code: [Select all] [Show/ hide]

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

mr_deb
Messages: 3
Registered: October 2006

Junior Member

i got the answer sir.Thanx very much

Report message to a moderator

Re: Delete even rows of a table [message #199639 is a reply to message #199624]

Wed, 25 October 2006 10:02

skooman
Messages: 913
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

Littlefoot
Messages: 21824
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.

Report message to a moderator

Re: Delete even rows of a table [message #199643 is a reply to message #199642]

Wed, 25 October 2006 10:13

skooman
Messages: 913
Registered: March 2005
Location: Netherlands

Senior Member

Maybe a new contest: find ways to randomly delete half of the records in a table Laughing

Report message to a moderator

Re: Delete even rows of a table [message #199668 is a reply to message #199643]

Wed, 25 October 2006 12:42

biohazardbill
Messages: 57
Registered: March 2006

Member

Maybe records were double entered and he is sorting somehow in an effort to remove dupes.

Report message to a moderator

Re: Delete even rows of a table [message #199673 is a reply to message #199668]

Wed, 25 October 2006 13:34

joy_division
Messages: 4963
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.

Report message to a moderator

Re: Delete even rows of a table [message #549203 is a reply to message #199567]

Wed, 28 March 2012 16:30

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!

Report message to a moderator

Re: Delete even rows of a table [message #549206 is a reply to message #549203]

Wed, 28 March 2012 17:05

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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?

Report message to a moderator

Re: Delete even rows of a table [message #549208 is a reply to message #549203]

Wed, 28 March 2012 17:15

cookiemonster
Messages: 13967
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.

Report message to a moderator

Re: Delete even rows of a table [message #549214 is a reply to message #549208]

Wed, 28 March 2012 17:55

muchObliged
Messages: 3
Registered: March 2012
Location: Iowa

Junior Member

Grouped by primary keys, worked like a charm

Report message to a moderator

Re: Delete even rows of a table [message #549216 is a reply to message #549214]

Wed, 28 March 2012 18:00

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Delete even rows of a table [message #549217 is a reply to message #549216]

Wed, 28 March 2012 18:58

muchObliged
Messages: 3
Registered: March 2012
Location: Iowa

Junior Member

I was answering your question...

Report message to a moderator

Re: Delete even rows of a table [message #549218 is a reply to message #549217]

Wed, 28 March 2012 19:19

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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.

Report message to a moderator

Re: Delete even rows of a table [message #575545 is a reply to message #199567]

Wed, 23 January 2013 23:58

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

Report message to a moderator

Re: Delete even rows of a table [message #575547 is a reply to message #575545]

Thu, 24 January 2013 00:32

sss111ind
Messages: 636
Registered: April 2012
Location: India

Senior Member

This Code can be rewriten as

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: Delete even rows of a table [message #575551 is a reply to message #575545]

Thu, 24 January 2013 01:28

Michel Cadot
Messages: 68760
Registered: March 2007
Location: Saint-Maur, France, https...

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

Code: [Select all] [Show/ hide]

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:

Code: [Select all] [Show/ hide]

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:

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: Delete even rows of a table [message #575552 is a reply to message #575547]

Thu, 24 January 2013 01:30

Michel Cadot
Messages: 68760
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Delete even rows of a table [message #575581 is a reply to message #199643]

Thu, 24 January 2013 07:58

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

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

Code: [Select all] [Show/ hide]

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

_jum
Messages: 577
Registered: February 2008

Senior Member

ok here is my version:

Code: [Select all] [Show/ hide]

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

Code: [Select all] [Show/ hide]

(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

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

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:

Code: [Select all] [Show/ hide]

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:

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

Re: Delete even rows of a table [message #575593 is a reply to message #575589]

Thu, 24 January 2013 09:35

_jum
Messages: 577
Registered: February 2008

Senior Member

@SY - Thanks - you are definitely an expert and would use the (later added) expert version with:

Code: [Select all] [Show/ hide]

    (SELECT rowid rid, ntile (2) over (order by mod(rownum+1,2)) tile

Report message to a moderator

Re: Delete even rows of a table [message #575594 is a reply to message #575593]

Thu, 24 January 2013 09:40

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Table will ALWAYS have rows with "even numbers"; except when table contains only ONE row!

Report message to a moderator

Re: Delete even rows of a table [message #575606 is a reply to message #575593]

Thu, 24 January 2013 11:23

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

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.

Report message to a moderator

Re: Delete even rows of a table [message #575635 is a reply to message #575551]

Thu, 24 January 2013 23:53

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

Report message to a moderator

Re: Delete even rows of a table [message #575636 is a reply to message #575635]

Thu, 24 January 2013 23:59

sss111ind
Messages: 636
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

Code: [Select all] [Show/ hide]

select 89%2 from dual;

Regards,
Nathan

Report message to a moderator

Re: Delete even rows of a table [message #575637 is a reply to message #575636]

Fri, 25 January 2013 00:06

MAHESH1244
Messages: 3
Registered: January 2013
Location: hyd

Junior Member

ITs sql server bro

Report message to a moderator

Re: Delete even rows of a table [message #575638 is a reply to message #575637]

Fri, 25 January 2013 00:52

sss111ind
Messages: 636
Registered: April 2012
Location: India

Senior Member

It's Oracle Faqs and not Sqlserver faqs.

Report message to a moderator

Re: Delete even rows of a table [message #575639 is a reply to message #575637]

Fri, 25 January 2013 00:52

Michel Cadot
Messages: 68760
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Previous Topic:	Export_Data_From_Table_To_Different_Excel
Next Topic:	Table Partitioning

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 20 02:20:38 CDT 2025