[dba-SQLServer] Delete all records of a particular type from a table except for one copy.

Jim,

You'll want to try this out on a copy of the table, of course, but I believe
it will accomplish what you want to do. I created the @MyCode variable,
thinking perhaps there might be times when you would want to delete all
except one record with MyCode values other than 'C'.

CREATE PROCEDURE [dbo].[sprocDelete_MyCode]
(@MyCode nvarchar(50))
AS Delete FROM MyTable where ID IN(SELECT     ID
FROM         dbo.MyTable
WHERE     (MyCode = @MyCode) AND (NOT (ID IN
                          (SELECT     TOP (1) ID
                            FROM          dbo.MyTable AS t
                            WHERE      (MyCode = @MyCode)))))
Kay


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Jim
Lawrence
Sent: Sunday, July 08, 2007 5:58 PM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] Delete all records of a particular type from a
table except for one copy.

Hi All:

This is a SQL question. It should be fairly easy but...

How would you delete all records from a table with a field set to a specific
value, all except one matching record?

Given: 
MyTable has 100 records with field MyCode set to either A, B or C. There
just happens, at this time, to be 37 records in the table with field MyCode
set to 'C'. 

Required: 
A SQL statement that when run deletes all matching records with field MyCode
equal to 'C' except for 1 record. (The number of records with field MyCode
set to 'C' varies of course.)

TIA
Jim    

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com