CODEWITHSUNDEEP
sqlsql-serverstored-proceduresdelete-row
Quick-gun Morgan
Quick-gun Morgan

Reputation: 338

How to delete multiple matching rows in stored procedure?

I have a following definition in my stored procedure.

DECLARE @DeletedRoleTag table( Role_key bigint, Tag_key bigint)

E.G. @DeletedRoleTag

Role_key    Tag_key
   1           2
   3           4

Now from my sql table (RoleTag) I want to delete rows which has matching rows with @DeletedRoleTag.

If there exist a row in RoleTag table with Role_key = 1 and Tag_key = 2 or Role_key = 3 and Tag_key = 4 it needs to be deleted. How do I do this?

Upvotes: 0

Views: 138

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

I would do this with an exists clause:

delete from RoleTag rt
   where exists (select 1
                 from @DeletedRoleTag drt
                 where drt.Role_key = rt.Role_key and
                       drt.Tag_key = rt.Tag_key
                );

Upvotes: 2

Kritner
Kritner

Reputation: 13765

try:

delete
from roleTag
from roleTag rt
inner join @DeletedRoleTag drt ON rt.role_key = drt.role_key
    and rt.tag_key = drt.tag_key

Upvotes: 0

Related Questions