25112
asked on
delete duplicates of 3 column combination
select COUNT(*),COL1,COL2,COL3 from tblOCDELStudent_1011 group by COL1,COL2,COL3 having COUNT(*) >1
gives records that have the combination of COL1,COL2,COL3 more than once..
how can we eliminate duplicates that all of them will have exactly one count only.. that is, the above query should give no results at all, and so we can delete ..
if the count is 3 it should have 2 deletes.. and if 4, then 3 and so forth..
gives records that have the combination of COL1,COL2,COL3 more than once..
how can we eliminate duplicates that all of them will have exactly one count only.. that is, the above query should give no results at all, and so we can delete ..
if the count is 3 it should have 2 deletes.. and if 4, then 3 and so forth..
awking00
Can you provide a description of the tblOCDELStudent_1011 table?
Is there an identity column or other unique value on each row?
If not, you might have to use a cursor with TOP.
If not, you might have to use a cursor with TOP.
Try
delete t
from tblOCDELStudent_1011 t
where pk = (select top 1 pk, COUNT(*),COL1,COL2,COL3 from tblOCDELStudent_1011 group by COL1,COL2,COL3 having COUNT(*) >1);
delete t
from tblOCDELStudent_1011 t
where pk = (select top 1 pk, COUNT(*),COL1,COL2,COL3 from tblOCDELStudent_1011 group by COL1,COL2,COL3 having COUNT(*) >1);
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you have a primary key PK_ID then it could be something like this
--delete tblOCDELStudent_1011
select * -- first test it as select, then change it to delete
from tblOCDELStudent_1011 as t1
inner join (
select COL1,COL2,COL3,min(PK_ID) as MIN_PK_ID
from tblOCDELStudent_1011
group by COL1,COL2,COL3 having COUNT(*) >1
) as t2 on t1.col1 = t2.col1
t1.col2 = t2.col2
t1.col3 = t2.col3
t1.PK_ID <> t2.MIN_PK_IDSOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
let me try your solutions..
the pk is the 3 columns ... col1/col2/col3 combination.. (of course, PK is not enforced yet.. that is the goal of this project )
the pk is the 3 columns ... col1/col2/col3 combination.. (of course, PK is not enforced yet.. that is the goal of this project )
ASKER
the table structure is
COL1 varchar 12
COL2 datetime
COL3 varchar 4
COL4 numeric 9
COL5 char 1
COL6 varchar 4
COL7 varchar 4
COL8 varchar 4
COL9 char 1
COL10 varchar 30
COL11 char 1
COL1 varchar 12
COL2 datetime
COL3 varchar 4
COL4 numeric 9
COL5 char 1
COL6 varchar 4
COL7 varchar 4
COL8 varchar 4
COL9 char 1
COL10 varchar 30
COL11 char 1
ASKER
awking00, your syntax gives this issue..
Windowed functions do not support integer indices as ORDER BY clause expressions.
scott, i am getting stuck in declaring your variables right.. i am working on it..
ashok- your last query - is that with PK or without..?
Windowed functions do not support integer indices as ORDER BY clause expressions.
scott, i am getting stuck in declaring your variables right.. i am working on it..
ashok- your last query - is that with PK or without..?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I think it will return the expected result ,,,,, you can analysis data by this.
select col1, col2, col3 from (
select row_number() over( partition by col1 order by col1
) as row,col1, col2, col3
from table_A) a --where row >1
and after that if you are satisfied then....
delete from (
select row_number() over( partition by col1 order by col1
) as row,col1, col2, col3
from table_A) a where row >1
select col1, col2, col3 from (
select row_number() over( partition by col1 order by col1
) as row,col1, col2, col3
from table_A) a --where row >1
and after that if you are satisfied then....
delete from (
select row_number() over( partition by col1 order by col1
) as row,col1, col2, col3
from table_A) a where row >1
Deepak,
select col1, col2, col3 from (
select row_number() over( partition by col1 order by col1
) as row, col1, col2, col3
from table_A) a where row > 1;
and after that if you are satisfied then....
delete a from (
select row_number() over( partition by col1 order by col1
) as row, col1, col2, col3
from table_A) a where row > 1;
You are missing "a" near the delete as shown above.
HTH
Ashok
select col1, col2, col3 from (
select row_number() over( partition by col1 order by col1
) as row, col1, col2, col3
from table_A) a where row > 1;
and after that if you are satisfied then....
delete a from (
select row_number() over( partition by col1 order by col1
) as row, col1, col2, col3
from table_A) a where row > 1;
You are missing "a" near the delete as shown above.
HTH
Ashok
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Did you try my method with the change in the order by clause?
ASKER
ashok/awking00
your syntax worked.. many thanks.. i tweaked scott's also to work.. (I did it in transaction and rolled back to test)
your syntax worked.. many thanks.. i tweaked scott's also to work.. (I did it in transaction and rolled back to test)
ASKER
thanks for the idea Deepak
Deepak,
delete from (
select row_number() over( partition by col1 order by col1
) as row,col1, col2, col3
from table_A) a where row >1;
Above does NOT work.
Do not say "I think....".
I tested it in SQL 2008 Management Studio.
After adding "b", it worked.
If you test it, you will know.
Thanks,
Ashok
delete from (
select row_number() over( partition by col1 order by col1
) as row,col1, col2, col3
from table_A) a where row >1;
Above does NOT work.
Do not say "I think....".
I tested it in SQL 2008 Management Studio.
After adding "b", it worked.
If you test it, you will know.
Thanks,
Ashok
By the way if you also add "a" to select like this....
select a.Menu_Date from
(
select row_number() over(partition by Menu_Date order by Menu_Date
) as row, Menu_Date
from DailyLunch) a where row > 1;
It works.
Ashok
select a.Menu_Date from
(
select row_number() over(partition by Menu_Date order by Menu_Date
) as row, Menu_Date
from DailyLunch) a where row > 1;
It works.
Ashok
Ashok
you r right
it is working with 'a'
i apologize
sorry for being mistake
you r right
it is working with 'a'
i apologize
sorry for being mistake
Deepak,
It's ok. We all make mistakes.
Sometimes it is better to test before making any judgements.
Thanks,
Ashok
It's ok. We all make mistakes.
Sometimes it is better to test before making any judgements.
Thanks,
Ashok