how to delete duplicate record in a table by using SQL query

[g.prabu]

how to delete duplicate record in a table by using SQL query

[pty]

how to delete duplicate record in a table by using SQL query

duplicate of what? all duplicates? why don't you have a unique identifier? do you have any validation? why are you here? why am i answering you?

[g.prabu]

i mean if a row occurs twise we have to delete one row of that two rows. no validation. by using sql query . if u can send me that query

[arunendra]

how to delete duplicate record in a table by using SQL query

I also faced the same kind of problem and solved it by myself as I never got good source.
I guess you have a table that has duplicate records but still you can identify them with a unique field, if so then follow this syntax, the theory is pretty simple, find the duplicate records with a subquery and then use the delete statement!!

for our query,I'm assuming that unique field is :U_F
the product code is " P_C
if you have multiple fields which you may have to match , then I am using another field which you may omit if you don't have and can increase the number of fields by adding them with AND.
so another field name i am using is : A_F

so the query will be:

DELETE *
FROM TableName WHERE U_F IN (select a.U_F from TableName as a, TableName AS b
WHERE a.P_C=b.P_C And a.A_F=b.A_F And a.U_F>b.U_F
and a.transaction_mode='some string/number');
'some string/number' represent the value that determines which records will be deleted!!
I hope this solves your problem!!! Mail me if you find this difficult and wanna get into more details!!:)
Bye
Arun

[eralper]

Hi,

You can find methods summarized with samples at the article named "How to delete duplicate records or rows among identical rows in a table where no primary key exists" at http://www.kodyaz.com/articles/delete-duplicate-records-rows-in-a-table.aspx

Eralper

[puppinoo]

Hi,
I registered just to thank you for this amazing article.
The query using the identity is simple and elegant!

Thanks a lot.
Pino

Hi,

You can find methods summarized with samples at the article named "How to delete duplicate records or rows among identical rows in a table where no primary key exists" at http://www.kodyaz.com/articles/delete-duplicate-records-rows-in-a-table.aspx

Eralper

[bsasiju]

the simple method remove duplicate rows on the table using the following query
first find the rows which have duplicates
SELECT identity column(fid)
FROM table
GROUP BY fid
HAVING COUNT(*) > 1

and then
SET ROWCOUNT 1
DELETE FROM table
WHERE fid = 1 AND fid = 1

[rado]

only remove duplicate records, select duplicated fields, and then apply group by

create table #temp same schema
INSERT INTO #temp SELECT field1,field2,field{n} FROM yourtable GROUP BY field1,field2,field{n}

DELETE OLDdata
INSERT INTO yourtable select * from #temp
DROP #temp
recommend: in transaction :)

[Nabi_Ahmad]

I have a table named "delivery" that has 10 fields. From that Iwant to count most delivered items, that means I want to Count duplicates based on ItemCode and Amount and group by shop names.

The looks like

ItemCode
Amount
Shopname
zz
zz
01
100
Telebrand

01
100
Telebrand

01
100
Telebrand

02
50
Telebrand

02
50
Telebrand

when I'll run the query it should give me data like this:

ShopNmae
Totaldelivery
Telebrand
4

that means duplicates will be counted on ItemCode and Amount. And most importantly it will always 1 less of each occurance. Like 01 and 100 occured 3 times but counted 2 and 02 and 50 occured 2 times but it will count 1 and the total count will be 4. And I have a field that is delivery data, Iwant to run this count operation within a fixed period of time using between comand or anything else. Can any of you please let me know the sql code for it ?

[mohammeds]

only remove duplicate records, select duplicated fields, and then apply group by

create table #temp same schema
INSERT INTO #temp SELECT field1,field2,field{n} FROM yourtable GROUP BY field1,field2,field{n}

DELETE OLDdata
INSERT INTO yourtable select * from #temp
DROP #temp
recommend: in transaction :)

Asalamvalikum
Hi This is mohammed azhar new to comm..

i need ur help how to update multiple columns (period,section)
in which data has interchanged

reply if possible today

[puneetkay]

Well guys.. im new for MS SQL.. i have a solution & it worked perfectly with me.

while creating table just add an extra attribute using identity keyword:

attributename int identity(1,1)

so everytime you insert any row, it will increase 1. so there will a unique number for every row :D

Regards!

[Jx_Man]

DELETE FROM emp e  WHERE rowid>(SELECT MIN(ROWID) FROM emp WHERE e.empno=empno)

[space1000]

Just solve the thing with a cursor

like
declare del_dups cursor
for
select * from <table_name>
group by column_name
having count(*) > 1

(column_name where the duplicates are)

open del_dups

fetch next from del_dups into ......

while @@fetch_status <> 0
begin
delete from table_name where current of cursor
fetch next from del_dups into ....
end

close del_dups
deallocate del_dups

For the update you should change te delete statement to

update table_name set ... = .... where current of cursor

[edgarkuhimbisa]

hey im workin out smthin hre will get back to u. i seem to be havin an idea

[gauravshar]

1. select distinct * into newtable from oldtable
2. truncate old table
3. insert into oldtable select* from newtable

using this we can delete the duplicate values/records from a table

[msi]

A few methods: sql-ex.ru