CODEWITHSUNDEEP
sql-server-2012sql-deleteinner-query
George
George

Reputation: 357

SQLServer Delete from table with list coming from another table

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.

Upvotes: 0

Views: 267

Answers (1)

markwatsonatx
markwatsonatx

Reputation: 3501

Try using an IN clause with a subquery:

DELETE FROM VariableTransaction WHERE VariableId = @VariableId
AND GroupUserId IN (SELECT Id FROM GroupUser WHERE GroupUserId = @GroupUserId)

Upvotes: 1

Related Questions