Deleting duplicate records 2

[obuspider]

I just discovered to records in my sql db that are duplicates. I need to delete them, but apparently have the wrong syntax. Can someone help? The records look like the following

site overp
003 1

 

[Zathras]

Unless there is something unique in at least one column to differentiate between the two records, you will have to delete them both and then re-insert one.

 

[obuspider]

Thanks. I thought that might be the case

 

[JamesLean]

In a normalised database there should always be something that uniquely identifies a row in a table - the Primary Key. Maybe you should consider implementing this on your table(s) to avoid this situation in the future? --James

 

[MeanGreen]

You could do this:

select distinct site,overp into #mytable from Table
go
truncate Table
go
Insert Table
Select * from #mytable
go

Hope this helps

 

[obuspider]

JamesLean,

You are right. Unfortunately, I'm working off a db that was created and is maintained by someone else.