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

Jim Lawrence accessd at shaw.ca
Sun Jul 8 17:57:30 CDT 2007


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    




More information about the dba-SQLServer mailing list