Deleting duplicate Fields from a Table

Question

Post reply

Deleting duplicate Fields from a Table

Ramesh-496278

SSC Veteran

Points: 287
More actions
January 9, 2008 at 3:39 am

#154050

Hi im having one Table called employee.. but there is no primary key column.. i would like to remove the duplicate field..
in the bellow example
Name 'Lissa' is repeating thrice
'Mike' is repeating twice
'Jack' is repeating twice
'Cathy' is also repeating twice
i dont want repeat these name again and again.. it should appear only once
id Name empssn
-- ----- ------
1 Jack 555-55-5555
2 Joe 555-56-5555
3 Fred 555-57-5555
4 Mike 555-58-5555
5 Cathy 555-59-5555
6 Lisa 555-70-5555
7 Jack 555-55-5555
8 Mike 555-58-5555
9 Cathy 555-59-5555
10 Lisa 555-70-5555
11 Lisa 555-70-5555
i have used the following query, but its not working
delete from employee
where (rowid, empssn)
not in
(select min(rowid), empssn from employee group by empssn);
plz help to solve this...........

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 1

This should work

set xact_abort on

begin tran

select distinct id, [Name], empssn into #tempEmployees

truncate table employees

insert employees(id, [Name], empssn)

select id, [Name], empssn

from #tempEmployees

drop table #tempEmployees

commit

HTH

Piotr

...and your only reply is slàinte mhath

Ramesh-496278 SSC Veteran Points: 287 More actions · Answer 2

Thank you very much for ur response...

Without temp Table is it possible?..

can u plz explain me

JWOL Hall of Fame Points: 3471 More actions · Answer 3

DELETE FROM e

FROM employee e

JOIN employee f ON e.Name = f.Name AND e.empssn = f.empssn

WHERE e.id < f.id

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

Ramesh (1/9/2008)
Thank you very much for ur response...
Without temp Table is it possible?..
can u plz explain me

Why do people always ask that? What's so poison about temp tables. Proper use of temp tables can make lightning fast code. Hell, Microsoft uses temp tables in many of their system stored procedures... 😉

--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)

venkata praveen SSC Enthusiast Points: 138 More actions · Answer 5

Hi Ramesh,

Try the follwoing query

Select * from Employee

where slno

not in( select min(slno) from Employee group by name,ssn)

If the above select gives your duplicate records then replace the select statement with Delete.

Be who you are and say what you feel,
because those who mind don't matter
and those who matter don't mind...

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

Including name in the sub-query will not guarantee unique SSN's. 😉

Still, I'd like to know... not that I'd do it with a Temp Table, but what's so important about avoiding Temp Tables on something like this? Is it because you guys are doing it from a GUI or some interface software or what?

--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)

venkata praveen SSC Enthusiast Points: 138 More actions · Answer 7

Hi Jeff,

There is no harm by using Temp tables. I also use for most of my complex update & select queries.

May be if somebody is not using a stored procedure and implementing direct SQL query from GUI then they may avoid Temp tables to make their query simpler.

Regards,

Praveen

Be who you are and say what you feel,
because those who mind don't matter
and those who matter don't mind...

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

Sure, Praveen... I just said that 😉 but thanks for the feedback.

And, my bad... Ramesh was the one that said it needed to be done without a temp table and I should have specifically asked him...

How 'bout it, Ramesh... why did you specifically ask for this to be done without a temp table?

--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)