DELETE duplicate records - need a simpler Solution
-
SQL Champs , I have Doubts pertaining to DELETE command to be used for a specific scenario :-
example # 1
Actually - Consider this data in the Table Salary_Mst
Name Salary
Muthu K 460000
Muthu K 460000
Now, My requirement is to delete the duplicate record only - that means after i write a sample Delete command & execute it, What must happen is that 1 record gets Deleted & one is Retained.
I would find it feasible if it had been something like :-
ID Name Salary
1 Muthu K 460000
2 Muthu K 460000
bcoz then we can use the WHERE clause & write a simpler delete command
Delete From Salary_Mst where ID = 2
example # 2
If we have data in table Employee_Mst like this :-
EmpName EmpDesignation
Muthu K ITA
Muthu K ITA
Muthu K ITA
Muthu K ITA
Sankara ASE
Sankara ASE
Sankara ASE
Sankara ASE
Now i need a Query which will leave the data in this table such :-
EmpName EmpDesignation
Muthu K ITA
Sankara ASE
I dont want to create the ID field into this table for both the Examples ?!
Can any1 help me in this regard ???
__________________
Thanx & Regards,Iyer Sankara S H [Kartik]
ASE ,
Tata Consultancy Services
-
There is proper solution for such kind of problem on microsoft site..just go in support.microsoft.com
Simple way you can do like this
select distinct EmpName,EmpDesignation
into table2 from table1
then drop table1 and rename tabel2 to table1
other way
select EmpName,EmpDesignation,count(EmpName)
into table2 from table1 group by
EmpName,EmpDesignation having count(EmpName)>1
delete from tabel1 where EmpName in
(select EmpName from tabel2)
insert into tabel1
select
select EmpName,EmpDesignation from tabel2
-
You can try something like this:
declare @Employee Table (EmpName varchar(10), EmpDesignation char(3))
insert @Employee values ('Muthu K', 'ITA')
insert @Employee values ('Muthu K', 'ITA')
insert @Employee values ('Muthu K', 'ITA')
insert @Employee values ('Muthu K', 'ITA')
insert @Employee values ('Sankara', 'ASE')
insert @Employee values ('Sankara', 'ASE')
insert @Employee values ('Sankara', 'ASE')
insert @Employee values ('Sankara', 'ASE')
set rowcount 1
while exists (select empName, EmpDesignation
from @employee
group by empName, EmpDesignation
having count(*) > 1)
begin
delete e
from @employee e
inner join (select empName, EmpDesignation
from @employee
group by empName, EmpDesignation
having count(*) > 1) dup on e.empName = dup.empName and e.EmpDesignation = dup.EmpDesignation
end
set rowcount 0
select * from @employee e
-
If you have a pot-wad of data, I suggest you create a sister table with a UNIQUE IGNORE DUPLICATES key on it... copy the data to the new table and the dupes will magically disappear... rename the old table as something else and rename the new table to the correct name... don't forget to add any indexes or keys you may have previously had.
Other than that, JeffB's solution will certainly do the trick...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Thanks to both Jeff B & Jeff M -
Jeff Moden - u have given me a real good idea !
1. Creating another Table ,
2. making the UNIQUE IGNORE DUPLICATES key "ON" & copy data into this new table ,
& 3. for all practical purposes Rename the old/current table & giving its name to newly created table [ adding Indexes & rest of the Keys wherever present]
something we SQL developers must try & use whenever we can ..........
Jeff B's solution is the one with the GroupBy-Having clause ; thats a proper query to solve this issue
This website does have Good Knowledgable members who give sound solutions
__________________
Thanx & Regards,Iyer Sankara S H [Kartik]
ASE ,
Tata Consultancy Services
-
...and thank you for the feedback...
By the way, if you do put such an index on the table, you will never have duplicates again.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply