Search code examples
sqlt-sqlsap-ase

Deleting all rows from table that share the same ID


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.


Solution

  • 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   |