ThomasRushton (10/24/2012)
This appears to do what you want:
use tempdbgo
declare @testtable table (id int, value int, name char(4))
insert into @testtable values
(1, 10, 'test'), (2, 5, 'prod'), (3, 10, 'test'), (4, 4, 'test'), (5, 4, 'test'), (6, 10, 'test')
select * from @testtable
delete from @testtable
WHERE id in (
select t2.id from @testtable t1 INNER JOIN @testtable t2 ON t1.id = t2.id - 1 INNER JOIN @testtable t3 ON t2.id = t3.id-1
WHERE t1.name = t2.name and t2.name = t3.name)
select * from @testtable
That's a lot of joins for a simple operation.
Maybe try something like this instead?
DELETE a
FROM @testtable a
WHERE EXISTS (SELECT 1
FROM @testtable b
WHERE (a.id = b.id+1 OR a.id = b.id-1)
AND a.NAME = b.NAME AND a.value = b.value);