I have a MySql database with 5 fields. There are two critical fields, Cfield1 and CField2. If for any two entries the values of Cfield1 are identical AND the values of Cfield2 are also identical, then I want to delete one of the two -- it does not matter which.
If there are three that are identical I want only 1 to remain and delete the rest.
I presume that the first thing to do is sort the db on Cfield1 and then Cfield2, but I am not sure what the rest of the coding should be.
Let me assume that you have another column -- say col3 -- that can differentiate the duplicate rows. Then you can do:
delete t
from t t join
(select cfield1, cfield2, min(col3) as min_col3
from t
group by cfield1, cfield2
) tt
using (cfield1, cfield2)
where t.col3 > tt.min_col3;
If you don't have such a column, then I might suggest creating a new table. The following uses a feature of MySQL that a pretty much totally discourage using, but it should do what you want:
create table temp_t as
select t.*
from t
group by col1, col2;
truncate table t; -- be very careful here, copy the data somewhere else first!
insert into t
select *
from temp_t;
You have to use count (*) and group by together:
select count (*),DuplicateField from table
group by DuplicateField
having count (*)>1
As a result ,You will get records . Or if you want delete I can tell you with an example of mine:
select
'RepetedRows',
SA.Saleref,
MIN(CAST(SA.SaleAdditionID as varchar(100)) ),
'DELETE sms.tblsaleadditions WHERE SaleAdditionID = ''' + MIN(CAST(SA.SaleAdditionID as varchar(100)) ) + ''''
--select *
from
sms.tblsaleadditions SA
inner join (
select
saleref,
additionref,
count(*) N,
SUM(AdditionAmount) nn
from
sms.tblsaleadditions
group by
saleref,
additionref
having count(*) > 1
) V ON SA.saleref = V.Saleref and SA.additionref = V.additionref
group by
SA.Saleref
Now you can copy all delete statements and run them.
What would this be in MySQL queries? We have this requirement, but the existing database is MySQL.
