Post reply

How to delete all but 1?

  • May 11, 2004 at 7:36 am

    #107920

    I have a table RoleModule (roleModule_ID, role_ID, Module_ID)

    I want to delete all rows from RoleModule table where role_ID=10 for example but I want to leave one RoleModule row where role_ID=10

    How to do that??

  • May 11, 2004 at 9:51 am

    #506008

    This assumes that the combination of ID values is unique -- specifically, that the roleModule_ID field is a unique identifier of some sort for the table.

    DELETE
    FROM RoleModule
    Where Role_ID = 10
      AND roleModule_ID Not In 
       (Select Min(roleModule_ID) FROM RoleModule Where Role_ID = 10)

    Note that this can be modified in multiple ways:

    • Change the subselect to not have the Role_ID specified, and GROUP BY Role_ID instead.  Now you can use this subselect to keep only one from each Role_ID
    • Group the Subselect by both Role_ID And Module_ID (in case of duplicates in the table)
    • Yadda, yadda, yadda...

     

  • May 11, 2004 at 5:09 pm

    #506062

    select count(*) where yada, yada

    then set rowcount = count(*) - 1

    delete where yada, yada

     

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • May 11, 2004 at 9:29 pm

    #506071

    and then:

    set rowcount 0

  • May 12, 2004 at 2:14 am

    #506085

    Hi!

    Probably this code will help you.....

    delete RoleModule

    where roleModule_ID not in (select max(roleModule_ID) from RoleModule

        group by Role_ID

        having count(*) > 1 )

    and Role_ID in (select Role_ID from RoleModule b

        group by Role_ID

        having count(*) > 1)

    This is generic for deleting the values that are occuring more than once in your table leaving one occurance.

     

    Indu

    Best Regards,

    Indu Jakhar

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply