sql

[shilpa.vp]

Hello All,
please may i know the query to identify duplicate values & delete those duplicates values from table.

Thanks in Advance!

[davide++]

Hi all.

To find the duplicate records you can use the GROUP BY and HAVING clauses. For example the query


SELECT NUMERO_CONTRATTO, COUNT(NUMERO_CONTRATTO)
FROM ORDINI
GROUP BY NUMERO_CONTRATTO
HAVING COUNT(NUMERO_CONTRATTO) > 1


finds all records from the table ORDINI that are duplicated according the valueS of NUMERO_CONTRATTO fields.
The deletion of duplicate records depends on your table is structured.

[shilpa.vp]

Thank you,
could you please tel how to delete only identified duplicate value from a table.

[davide++]

Hi all.
As I said the deletion depend on how the table is structured; moreover it's important to know what database you're using.
The query I wrote find all duplicate row according a specific field; for example I found that for NUMERO_CONTRATTO = '1000005199501' there are two rows; my table ORDINI has a primary key, ORDINE_ID, so I can choose what record must be deleted with a simple DELETE command:


DELETE ORDINI
WHERE ORDINE_ID = ...


If you aren't interesting about record that must be deleted you can use a command like this:


DELETE ORDINI
WHERE NUMERO_CONTRATTO = '1000005199501'
AND ROWNUM = 1 -- it deletes one record only


It works on Oracle; if you're using SqlServer see TOP clause.

I hope this will help you

[shilpa.vp]

Thanks,
is there any single query to identify duplicate rows in a table not a specific field & same time it has to delete duplicate rows.

[davide++]

Well.

The previous query to find duplicated rows can be easily extended to treat more than one field. For example the query

[CODE]
SELECT NUMERO_CONTRATTO, STATO_ORD_ID, COUNT(NUMERO_CONTRATTO)
FROM ORDINI
GROUP BY NUMERO_CONTRATTO, STATO_ORD_ID
HAVING COUNT(NUMERO_CONTRATTO) > 1
[CODE]

finds all row that are duplicated according two fields.
If your requirements is to find rows which are duplicated according all fields you'll put in GROUP BY clause all fields; but this's an "extreme" situation where you have a table without the primary key, so a bad designed table.

Regard if it's possible to do all using a single query depends on what db you're using; but in general it's quite difficult.

[shilpa.vp]

Thaks,
i have table with primary key,i'm able to identify duplicate rows but query unable to delete single duplicate row.
ex: table named Employee with 2 fields EmployeeID & EmployeeName

Delete from Employee where EmployeeName="xxxx";
this query is deleting 2 rows
please may i know what is solution for this & is there any single query to identify duplicate & delete at the same time.

[cjard]

Er.. if the table has a primary key, how can it posssibly have duplicate rows? Given that a primary key is a column or columns that uniquely identify a row, your table cannot possibly have any duplicates in the key

[sraheem]

Delete rows using primary key like employee id or employee code which is unique.

You can say 2 rows are duplicated if all the columns for those two rows have identical values.