| 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.
|
|
|
|
|
|
|
|
| Re: Delete even rows of a table [message #199607 is a reply to message #199576] |
Wed, 25 October 2006 04:20  |
 |
Littlefoot
Messages: 21825
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 #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 #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:30I 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 #575551 is a reply to message #575545] |
Thu, 24 January 2013 01:28 |
 |
Michel Cadot
Messages: 68763
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
MAHESH1244 wrote on Thu, 24 January 2013 06:58WITH 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 #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:13Maybe 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 |
_jum
Messages: 577
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 
[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:44ok 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 |
_jum
Messages: 577
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 #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.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
