I have the following table:
Name | ID | date |
Login | 1 | somedate |
Command | 1 | somedate |
Command | 1 | somedate |
Login | 2 | somedate |
Command | 1 | somedate |
Command | 2 | somedate |
Logout | 1 | somedate |
Command | 2 | somedate |
I want to delete from the table everything between a login and a logout that share the same ID, but keep everything else there. The somedate fields are datetime. There could be more logins/logouts in the table and there will be Logins that don't have a corresponding logout. I want that to remain there because the logout will appear eventually.
I was thinking of using a cursor. Which would be the best approach for performance too? The final table could have a few millions of rows.
After the delete the table should look like this:
Name | ID |
Login | 2 |
Command | 2 |
Command | 2 |
Edit: Delete everything between a Login and a Logout, including the rows with Login/Logout.
Assuming that every logout has a corresponding login you could try this:
DELETE
FROM yourTable
WHERE ID IN
(
SELECT ID
FROM yourTable
WHERE Name LIKE 'Logout'
)
The result would be:
Name | ID |
Login | 2 |
Command | 2 |
Command | 2 |
If you want to have the rows with "Login" and "Logout" too you could do this:
DELETE
FROM yourTable
WHERE ID IN
(
SELECT ID
FROM yourTable
WHERE Name LIKE 'Logout'
)
AND Name NOT LIKE 'Login'
AND Name NOT LIKE 'Logout'
You would get this result:
Name | ID |
Login | 1 |
Login | 2 |
Command | 2 |
Logout | 1 |
Command | 2 |