Delete Dup record (newbie quesyion)

[scroce]

being used to working with jet, if I saw a duplicate record, i could just hi-lite the row and press delete key and it would go away.

However, it seems in EM and/or Query Analyzer, you can't do that.

I could write a DELETE WHERE statment, but that would delete both my records.

what is the most straightforward way of getting around this.

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[Pattycake245]

If your table has a unique identifier in it, you could use that to delete the duplicates you need. The basic syntax would be something like:

DELETE T
FROM Table T LEFT JOIN
(SELECT MaxID = Max(ID)
FROM Table
GROUP BY Whatever
) T2 ON T.MaxID = T2.ID
WHERE T2.MaxID IS NULL

Tim

 

[scroce]

ok - am i correct in saying then, that there is no easy way to just click and delete a record in SQLServer, and that you need to write a procedure like this, save it, and use it as a template when you need to do something similar.

i'm trying to to get my sea legs in SQL server

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[Pattycake245]

Well, if you want to do it by clicking on a row and deleting it, you can open the table in EM click on the row and delete it. You'll get a warning message double-checking to ensure you want to delete the row. Although, if you are trying to "get your feet wet" with SQL, you shouldn't do this and learn to write queries to do this instead.

Tim

 

[scroce]

ah thank you. - silly me, that's painfully obvious.

in this case i was really looking for a simple way to do it. Right now, I'm spending a lot of time analyzing the difference in flavor of Jet SQL versus SQLServer SQL.

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[iBe]

I could write a DELETE WHERE statment

A delete/where is the easiest way to do it.

delete _tableName_ 
where _columnName_ = '_someValue_'

For all but the smallest of projects, finding and deleting a specific table/row in Enterprise Manager is unessecary and slow. Anything you can accomplish using the GUI you can accomplish using a query.


8]

 

[iBe]

If you can't get specific enough (easily enough) with a query to delete a specific row, you may want to consider changing the design of the database, or using temp tables to add a unique identifier for each row and then querying from the temp tables.

 

[scroce]

i predict this to be a one time thing. somehow a dup record got in there - probably in my noodling around with the data.


I am a nobody, and nobody is perfect; therefore, I am perfect.