How Delete all but 1?? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How Delete all but 1??

Author

Topic

mariechristine
Starting Member

27 Posts

Posted - 2004-05-11 : 09:04:09

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

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-05-11 : 09:20:17

Pick the roleModule_ID and Module_ID you want to keep and put these in the where clause...

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-11 : 09:51:35

DELETE rm1
FROM
RoleModule rm1
LEFT OUTER JOIN (
SELECT MAX(roleModule_ID)
FROM RoleModule
WHERE role_ID = 10) rm2 ON rm1.roleModule_ID = rm2.roleModule_ID
WHERE
rm1.role_ID = 10
AND rm2.roleModule_ID IS NULL

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-05-11 : 15:19:25

delete from
RoleModule
where role_ID = 10 and
roleModule_ID <> (Select Max(roleModule_ID) from RoleModule where role_id=10)

assuming roleModule_ID is the primary key of the table.

and to avoid this in the future, if this is a many-to-many table, do NOT just add an identity as the primary key !! the primary key constraint for this table should be a combination of role_ID and module_ID. Then you won't have the duplication problems.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources