Delete data

Question

Post reply

Delete data

joemai

Hall of Fame

Points: 3496
More actions
March 20, 2006 at 2:26 pm

#113244

Hi All,
Sorry for this kind of basic query, but I don't know how to do it. I have a table with autonumber field, custnbr, email. There is a lot of duplications in there.
Ex. Id     Custnbr      Email
     1       aaa          test@yahoo.com
     2       aaa          test@yahoo.com
     3       aaa          test@yahoo.com
This table is huge. I'd like to get rid of duplication and only keep the last record.
    3    aaa test@yahoo.com
How can I contruct my sqlstatement to delete data?
THanks for any helps.
Minh Vu

Viewing 4 posts - 1 through 3 (of 3 total)

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

noeld SSC Guru Points: 96590 More actions · Answer 1

Select Max(Id) Id, Custnbr, Email

into #t1

from Yourtable

group by Custnbr, Email

having count(*) > 1

delete D

from YourTable D join #t1 t

on D.Custnbr =t.Custnbr and D.Email = t.Email and t.ID <> D.ID

-- clean up

Drop table #t1

select * from YourTable -- no dupes !!

Cheers,

* Noel

joemai Hall of Fame Points: 3496 More actions · Answer 2

joemai

Hall of Fame

Points: 3496

March 20, 2006 at 4:13 pm

#627752

thanks very much. I worked for me.

Minh Vu

Lowell SSC Guru Points: 323518 More actions · Answer 3

you might want to consider adding a constraint to prevent the data from getting messed up in the future too:

alter table sometable add constraint UQ_Custnbr_Email unique(Custnbr, Email)

you'd have to add code to capture the error and tell the user that the customer number/email combination already exists, but it'd prevent duplicate data in the future.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!