There seem to be a few questions like this, but none are exactly the same, and so:
I need to find a way to delete a row in one table where there is a row in another table in which two of its fields are equal to two fields from the original table. (In the following example, it will look like this: I need to find a way to delete a line in @All with @ All.Stall = @ Grouped.Stall and @ All.Fruit = @ Grouped.Fruit)
For example:
@All: table to delete rows:
Stall Fruit ------------------- John Apples John Pears John Pineapple Mary Apples Mary Apples Mary Pears Mary Pineapple
@Grouped: Table for getting rows to delete from:
Stall Fruit ------------------- Mary Apples
The summary table should look like this:
Stall Fruit ------------------- John Apples John Pears John Pineapple Mary Pears Mary Pineapple
Note that the two lines that contain: Mary | Apples have disappeared.
In my life, I can’t understand how to do this, and can make him delete all three lines containing Apples, and not leave one John | Apples
Here are the requests for creating two temporary tables if someone can help:
@All - Table to delete rows
@Grouped - table with fields to remove from @All
DECLARE @All TABLE( Stall varchar(10), Fruit varchar(10), StallFruitID int ) DECLARE @Grouped TABLE( Stall varchar(10), Fruit varchar(10) ) INSERT INTO @All (Stall,Fruit,StallFruitID) VALUES('John','Apples',1) INSERT INTO @All (Stall,Fruit,StallFruitID) VALUES('John','Pears',1) INSERT INTO @All (Stall,Fruit,StallFruitID) VALUES('John','Pineapple',1) INSERT INTO @All (Stall,Fruit,StallFruitID) VALUES('Mary','Apples',1) INSERT INTO @All (Stall,Fruit,StallFruitID) VALUES('Mary','Apples',2) INSERT INTO @All (Stall,Fruit,StallFruitID) VALUES('Mary','Pears',1) INSERT INTO @All (Stall,Fruit,StallFruitID) VALUES('Mary','Pineapple',1) INSERT INTO @Grouped (Stall,Fruit) VALUES('Mary','Apples')