How to delete rows from same table?

I have a table that logs requests and responses. The table has a timestamp column and a transaction type column with 2 possible values: req, resp. Regardless of the why, I have resp rows that have no matching req row.

I am trying to delete all开发者_如何学JAVA resp rows that don't have a matching req row with the same timestamp. Any ideas?

What i need in pseudocode is:

Delete all rows in table where transaction type equals resp and has no matching req row with the same timestamp.

---

Try this:

Delete Table t
Where TxType = 'resp'
  And Not Exists
   (Select * From Table
    Where timestamp = t.timestamp
       and TxType = 'req')

(Inside of a transaction so you can roll it back if it fails!)

and oh yeah, Chris Lively from below raises an excellent point.. When you refer to a "TimeStamp" are you speaking generically? or are you talking about the SQL Server Timestamp data type that is guaranteed to be unique ?? If the former, and you are actually using a datetime or some such, then Are you sure you can't have two or more different requests at the exact same time ??

---

This should work for you

delete T1
from @T as T1
where
  T1.[Type] = 'resp' and
  T1.[TimeStamp] not in (select [TimeStamp]
                         from @T as T2
                         where T2.[Type] = 'req')

And some test code

declare @T table ([TimeStamp] datetime, [Type] varchar(5))

insert into @T values
('2001-01-01', 'req'),
('2001-01-01', 'resp'),
('2002-01-01', 'resp'),
('2003-01-01', 'req')

print 'Before delete'
select *
from @T

delete T1
from @T as T1
where
  T1.[Type] = 'resp' and
  T1.[TimeStamp] not in (select [TimeStamp]
                         from @T as T2
                         where T2.[Type] = 'req')

print 'After delete'
select *
from @T

Output

(4 row(s) affected)
Before delete
TimeStamp               Type
----------------------- -----
2001-01-01 00:00:00.000 req
2001-01-01 00:00:00.000 resp
2002-01-01 00:00:00.000 resp
2003-01-01 00:00:00.000 req

(4 row(s) affected)

(1 row(s) affected)

After delete
TimeStamp               Type
----------------------- -----
2001-01-01 00:00:00.000 req
2001-01-01 00:00:00.000 resp
2003-01-01 00:00:00.000 req

(3 row(s) affected)

---

;WITH T AS
(
SELECT *, RANK() OVER (PARTITION BY [TimeStamp] ORDER BY [Type]) RN
FROM YourTable
)
DELETE FROM T 
WHERE Type='resp' AND RN=1

---

something like the following ought to do it.

delete mytable
  from mytable t1
     left outer join mytable t2 
         and (t2.TimeStamp = t1.TimeStamp)
         and (t2.TransactionType = 'req')
  where (t2.TimeStamp is null)
    and (t1.TransactionType = 'resp')

After looking at this, is the tie inbetween the two records really a timestamp? It just seems to me that the timestamps would be different and instead you have some other key that links the two together.

---

DELETE FROM YourTable
WHERE Timestamp IN (
  SELECT Timestamp FROM YourTable WHERE TransactionType = 'resp'
  EXCEPT
  SELECT Timestamp FROM YourTable WHERE TransactionType = 'req'
)