To remove repeated data from table?

Go down

To remove repeated data from table?

Post by Naveen on Thu Mar 18, 2010 10:20 am

I have 2 or more records containing same data
For instance
SNO NAME AGE
1001 ANAND 21
1001 ANAND 21

How do i delete only one row from the table?

Naveen

Posts : 15
Points : 21
Join date : 2010-03-05
Age : 30

View user profile

Back to top Go down

Re: To remove repeated data from table?

Post by anand on Thu Mar 18, 2010 11:48 am

use distinct keyword.. it may help
avatar
anand

Posts : 55
Points : 70
Join date : 2010-02-26
Age : 31

View user profile

Back to top Go down

Re: To remove repeated data from table?

Post by Christopher on Thu Mar 18, 2010 11:59 am

DISTINCT keyword can be used only to query for unique records. it wont delete repeated records.
avatar
Christopher
Admin

Posts : 240
Points : 429
Join date : 2010-02-26
Age : 30

View user profile http://unixcpp.forumotion.com

Back to top Go down

Re: To remove repeated data from table?

Post by Naveen on Thu Mar 18, 2010 12:40 pm

@anand
distinct word can't be used, i wan record 2 be deleted...

Naveen

Posts : 15
Points : 21
Join date : 2010-03-05
Age : 30

View user profile

Back to top Go down

Re: To remove repeated data from table?

Post by Maithreyi on Thu Mar 18, 2010 4:31 pm

Don't remember exactly,but I think join command would help!

Maithreyi

Posts : 76
Points : 142
Join date : 2010-03-03
Age : 30
Location : Haldia

View user profile

Back to top Go down

Re: To remove repeated data from table?

Post by Saradha Kannan on Thu Mar 18, 2010 4:46 pm

I don't think its join.. I think its UNION... But that shud be between 2 tables rite..

Saradha Kannan

Posts : 25
Points : 25
Join date : 2010-03-04

View user profile

Back to top Go down

Re: To remove repeated data from table?

Post by Maithreyi on Thu Mar 18, 2010 4:52 pm

No clue! I vaguely recall self-join in combination with something!

Maithreyi

Posts : 76
Points : 142
Join date : 2010-03-03
Age : 30
Location : Haldia

View user profile

Back to top Go down

Re: To remove repeated data from table?

Post by DineshThangaraju on Thu Mar 18, 2010 5:09 pm

I recall that it can be done using cursor concept..

DineshThangaraju

Posts : 18
Points : 19
Join date : 2010-02-26
Age : 30
Location : India

View user profile

Back to top Go down

Re: To remove repeated data from table?

Post by urvershi on Thu Mar 18, 2010 5:42 pm

delete from <tablename> where rownum=1;

urvershi

Posts : 21
Points : 70
Join date : 2010-02-26

View user profile

Back to top Go down

Re: To remove repeated data from table?

Post by Christopher on Thu Mar 18, 2010 5:51 pm

@urvershi
it s not working?? it jus deletes 1st record.
avatar
Christopher
Admin

Posts : 240
Points : 429
Join date : 2010-02-26
Age : 30

View user profile http://unixcpp.forumotion.com

Back to top Go down

Re: To remove repeated data from table?

Post by anand on Thu Mar 18, 2010 5:56 pm

create table newtable as select distinct * from oldtable;

here the new table will contain the distinct values ...
avatar
anand

Posts : 55
Points : 70
Join date : 2010-02-26
Age : 31

View user profile

Back to top Go down

Re: To remove repeated data from table?

Post by Naveen on Thu Mar 18, 2010 6:24 pm

@anand
I don't wan 2 create new table, frm de existin table i wan 2 delete it...

Naveen

Posts : 15
Points : 21
Join date : 2010-03-05
Age : 30

View user profile

Back to top Go down

Re: To remove repeated data from table?

Post by Naveen on Thu Mar 18, 2010 6:25 pm

@dinesh
do u remember the command?

Naveen

Posts : 15
Points : 21
Join date : 2010-03-05
Age : 30

View user profile

Back to top Go down

Re: To remove repeated data from table?

Post by anand on Fri Mar 19, 2010 10:54 am

SQL> select rownum,sno,name,age from anan8;

ROWNUM SNO NAME AGE
---------- ---------- ---------- ----------
1 1001 anand 21
2 1001 anand 21

;;; now u can delete watever row u want to delete.....
here rownum s default field..

SQL> delete from anan8 where rownum = 1;

1 row deleted.

SQL> select * from anan8 ;

SNO NAME AGE
---------- ---------- ----------
1001 anand 21

@:it works urvershi....
avatar
anand

Posts : 55
Points : 70
Join date : 2010-02-26
Age : 31

View user profile

Back to top Go down

Re: To remove repeated data from table?

Post by Christopher on Fri Mar 19, 2010 11:14 am

it jus deletes a row. even it s not working for all row numbers???
the question is to delete all the repeated rows. how do i know the exact row numbers of repeated data if i have 5k records.
avatar
Christopher
Admin

Posts : 240
Points : 429
Join date : 2010-02-26
Age : 30

View user profile http://unixcpp.forumotion.com

Back to top Go down

Re: To remove repeated data from table?

Post by Naveen on Fri Mar 19, 2010 11:24 am

@anand
How do we know the row number of repeated rows?

Naveen

Posts : 15
Points : 21
Join date : 2010-03-05
Age : 30

View user profile

Back to top Go down

It will I think

Post by abdulraja on Fri Mar 19, 2010 12:59 pm

delete from tablename where row = 12;
avatar
abdulraja

Posts : 1
Points : 1
Join date : 2010-03-17
Age : 32
Location : Dindigul

View user profile

Back to top Go down

Re: To remove repeated data from table?

Post by Naveen on Fri Mar 19, 2010 1:06 pm

@above
how do yo know tat which row is repeated in table?
I jus wan 2 delete all repeated rows...

Naveen

Posts : 15
Points : 21
Join date : 2010-03-05
Age : 30

View user profile

Back to top Go down

Re: To remove repeated data from table?

Post by Mahee on Fri Mar 19, 2010 1:26 pm

Guys I Hope This will work....
For having this work done you need to choose the column by which the rows differ for eg,

no name addr
12 Raja chennai
12 Raja chennai
13 Raja chennai
14 Raja chennai

The only thing is you have to mention whether you have check the similarity using particular column or all the column

mention all the column name in the below command if you want to check repetition based on all the column or else mention only the column names based on which you like to check repetation of data...

the command is,

sql> delete <tablename>
where rowid not in (
Select Min(Rowid) from <tablename> group by column1,column2,....columnN)

The above command will delete all the repeating datas in the table
avatar
Mahee

Posts : 29
Points : 233
Join date : 2010-03-03
Location : Haldia

View user profile

Back to top Go down

Re: To remove repeated data from table?

Post by Christopher on Fri Mar 19, 2010 3:07 pm

sql> delete <tablename>
where rowid not in (
Select Min(Rowid) from <tablename> group by column1,column2,....columnN)
it works perfectly.
avatar
Christopher
Admin

Posts : 240
Points : 429
Join date : 2010-02-26
Age : 30

View user profile http://unixcpp.forumotion.com

Back to top Go down

Re: To remove repeated data from table?

Post by Naveen on Fri Mar 19, 2010 3:25 pm

yeaaa mahee it works perfectly...
thanx man... kudos...

Naveen

Posts : 15
Points : 21
Join date : 2010-03-05
Age : 30

View user profile

Back to top Go down

Re: To remove repeated data from table?

Post by abhisheksingh on Fri Mar 19, 2010 4:03 pm

delete from table_name tb_var1 where rowid not in (select min(rowid) from table_name tb_var2 where tb_var1.name=tb_var2.name);

try this.. it must work..
avatar
abhisheksingh

Posts : 10
Points : 117
Join date : 2010-03-09
Age : 31
Location : Haldia

View user profile

Back to top Go down

Re: To remove repeated data from table?

Post by Mahee on Fri Mar 19, 2010 4:17 pm

@abi
The above command works if we check for all the column using 'and' operator instead of checking only with 'name' column

If we check only 'name' column it will delete all the data with same name, without checking other column...
for eg,
Two employee may have same name and different id.. If use ly name then it will delete 2nd employee with different id..

So if we use this command we have to check for every column....
avatar
Mahee

Posts : 29
Points : 233
Join date : 2010-03-03
Location : Haldia

View user profile

Back to top Go down

Re: To remove repeated data from table?

Post by urvershi on Wed Mar 24, 2010 11:03 am

delete <tablename> <table aliase1> where
<tablealiase1>.rowid < any
(select <tablealiase2>.rowid from <tablename> <tablealiase2>
where
<tablealiase1>.<columnname1>=<tablealiase2>.<columnname1> and <tablealiase1>.<columnname2>=<tablealiase2>.<columnname2> and ......
.... and <tablealiase1>.<columnnamen>=<tablealiase2>.<columnnamen>;

urvershi

Posts : 21
Points : 70
Join date : 2010-02-26

View user profile

Back to top Go down

Re: To remove repeated data from table?

Post by Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum