Can we delete tuples in a table based on date column? And how?

Showing Answers 1 - 5 of 5 Answers

Brijesh Singh

  • Oct 17th, 2005
 

use this SQL query :delete from student_tab A where ROWID > ( select min(ROWID) from student_tab B where A.roll_number = B.roll_number);Note : Table Name = student_tab Key to table = roll_number

  Was this answer useful?  Yes

vishnu

  • Sep 5th, 2006
 

If you want to delete rows with given DATE

DELETE FROM TABLE_NAME WHERE DATE_COLUMN = GIVEN DATE;



If you want to delete rows >= given DATE

DELETE FROM TABLE_NAME WHERE DATE_COLUMN >= GIVEN DATE;



If you want to delete rows <= given DATE

DELETE FROM TABLE_NAME WHERE DATE_COLUMN <= GIVEN DATE;



If you want to delete rows BETWEEN DATE1 and DATE2

DELETE FROM TABLE_NAME WHERE DATE_COLUMN BETWEEN DATE1 AND DATE2;

  Was this answer useful?  Yes

SQL> create table m( n number);

Table created.


  1* insert into m values(&1)
SQL> /
Enter value for 1: 1
old   1: insert into m values(&1)
new   1: insert into m values(1)

1 row created.

SQL> /
Enter value for 1: 2
old   1: insert into m values(&1)
new   1: insert into m values(2)

1 row created.

SQL> /
Enter value for 1: 3
old   1: insert into m values(&1)
new   1: insert into m values(3)

1 row created.

SQL> /
Enter value for 1: 4
old   1: insert into m values(&1)
new   1: insert into m values(4)

1 row created.

SQL> /
Enter value for 1: 1
old   1: insert into m values(&1)
new   1: insert into m values(1)

1 row created.

SQL> /3
Enter value for 1: 3
old   1: insert into m values(&1)
new   1: insert into m values(3)

1 row created.

SQL> /
Enter value for 1: 4
old   1: insert into m values(&1)
new   1: insert into m values(4)

1 row created.

SQL> /
Enter value for 1: 1
old   1: insert into m values(&1)
new   1: insert into m values(1)

1 row created.

SQL> /
Enter value for 1: 3
old   1: insert into m values(&1)
new   1: insert into m values(3)

1 row created.

SQL> select * from m;

         N
----------
         1
         2
         3
         4
         1
         3
         4
         1
         3

9 rows selected.

SQL> commit;

Commit complete.

SQL> delete from m where rowid not in( select min(rowid) from m group by n);

5 rows deleted.

SQL> select * from m;

         N
----------
         1
         2
         3
         4

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions