• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

Deleting Dulpicate row

Dear All,
Is there Any query ,Which will Delete Duplicate Rows from the table which does not  have primary key..please
Help me out
0
skumaravelan
Asked:
skumaravelan
  • 3
  • 2
  • 2
  • +1
1 Solution
 
whbramCommented:
What database are you using?
0
 
kdg2000Commented:
Algorithm 1:
1. Opens Recordset of all unique values
2. In cycle there is an exhaustive search of all values.
3. In cycle: We define how many counterparts of current value in the table. If them more than 1, then is remembered current value, we delete all and is added it(him).
4. End of cycle
5. Closing RecordSet.

Algorithm 2:
1. Opens Recordset of all values sorted on increase
2. We create a variable, in which the previous value is stored
2. In cycle there is an exhaustive search of all values.
3. In cycle: whether We define equally given data previous. If yes - then is deleted it, differently - variable is assigned current value
4. End of cycle
5. Closing RecordSet.

In SQL Server it is done(made) so:

declare @S as varchar (8000)
/* The cursor of the necessary type */ is declared
declare Data_Pr_F scroll cursor
/* Choice of not repeating values */
for select distinct DuplicateRows from [table]
open Data_Pr_F
FETCH FIRST from Data_Pr_F into @S
      /* Check of the end of the table */
      while @@ fetch_status < > -1
      begin
           /* If there is more than one counterpart, then all values are deleted and one unique value is added */
           if (select count (DuplicateRows) from [table]) > 1
           begin
                delete * from [table] where DuplicateRows = S
                insert into [table] values (@S)
           end
           /* Transition to the following string(line) */
           FETCH next from Data_Pr_F into @S
      end
/* Deleting the cursor */
DEALLOCATE Data_Pr_F
0
 
kretzschmarCommented:
maybe also

step one: create a table with none duplicate values
  create table tmp as Select distinct * from originaltable;

step two: empty sourcetable
  delete from originaltable;

step three: store back
  insert into originaltable select * from tmp;

step four: drop tmp-table
  drop table tmp;


(may not work on all databases)

meikl ;-)
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
skumaravelanAuthor Commented:
Dear All,
I have tried al the option u all said befoe.My question is,is there any way to do the same with writing a procedure or function
0
 
kretzschmarCommented:
it would help,
if you let us know the database you have
0
 
skumaravelanAuthor Commented:
Hi kretzschmar,
I have oracle 8 as my database.
0
 
kdg2000Commented:
Oracle 8 is very similar on MS SQL Server. Some finishings are certainly necessary. For example environment variable @@ fetch_status for certain is not present in Oracle, but 100 % there are a similar variable. Anyway standard SQL 92 is uniform both for Oracle and for MS SQL Server.
I think these small finishings as you seem to me can make.
0
 
skumaravelanAuthor Commented:
Dear KDq2000,
Thank You very much for ur valuable reply.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now