0

I have a DELETE requiring a where with an AND in the WHERE clause to pick the rows. One the of values in the WHERE is actually a bunch of ids. I could accomplish with programming to get the list of user ids and then looping using the list.

This query returns a list of GroupUserIds
SELECT Id FROM GroupUser WHERE GroupUserId = @GroupUserId

I this wish to delete from VariableTransaction for each GroupUserId

Foreach @GroupUserId in GroupUserIds
DELETE FROM VariableTransaction WHERE VariableId = @VariableId AND GroupUserId = @GroupUserId

There should be a way to combine these into a single SQL statement but looking at all the examples I cannot figure out a solution, the where with the AND complicates it.

George
  • 307
  • 1
  • 5
  • 12

1 Answers1

1

Try using an IN clause with a subquery:

DELETE FROM VariableTransaction WHERE VariableId = @VariableId
AND GroupUserId IN (SELECT Id FROM GroupUser WHERE GroupUserId = @GroupUserId)
markwatsonatx
  • 3,391
  • 2
  • 21
  • 19