How to delete all but 1?

• MC-142370

  Old Hand

  Points: 390

  More actions

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

• brendt hess

  Hall of Fame

  Points: 3256

  More actions

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

   

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736270

  More actions

  May 11, 2004 at 5:09 pm

  #506062

  select count(*) where yada, yada

  then set rowcount = count(*) - 1

  delete where yada, yada

   

• Chris Scott

  SSC Veteran

  Points: 297

  More actions

  May 11, 2004 at 9:29 pm

  #506071

  and then:

  set rowcount 0

• Indu Jakhar

  Old Hand

  Points: 337

  More actions

  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