CODEWITHSUNDEEP
sqlsql-servert-sqlsql-delete
njminchin
njminchin

Reputation: 452

Delete Rows in one table based on row values in this and another table

There seem to be a few questions like this, but none exactly the same, so here goes:

I need to find a way to delete a row in one table where there is a row in another table that has two of its fields equal to two fields from the original table. (In the following example, this will read: I need to find a way to delete a row in @All that has @All.Stall = @Grouped.Stall and @All.Fruit = @Grouped.Fruit)

For example:

@All: Table to have rows deleted:

Stall       Fruit
-------------------
John        Apples
John        Pears
John        Pineapple
Mary        Apples
Mary        Apples
Mary        Pears
Mary        Pineapple

@Grouped: Table to get rows to delete from:

Stall       Fruit
-------------------
Mary        Apples

The resultant table should look like this:

Stall       Fruit
-------------------
John        Apples
John        Pears
John        Pineapple
Mary        Pears
Mary        Pineapple

Note that the two rows that contain: Mary | Apples are gone.

For the life of me, I cannot figure out how to do this, and can only get it to remove all three rows that contain Apples and not leave the one John | Apples.

Here are the queries to create the two temporary tables, if anyone is able to help:

@All - Table to have rows deleted

@Grouped - Table with fields to look-up to delete 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')

Upvotes: 7

Views: 9424

Answers (2)

raj
raj

Reputation: 129

Try this 

DELETE FROM @All WHERE Stall="Mary" AND Fruit="apples";

Upvotes: 0

John Woo
John Woo

Reputation: 263723

DELETE  a
FROM    table1 a
        INNER JOIN table2 b
            ON  a.Stall = b.Stall AND
                a.Fruit = b.Fruit

Upvotes: 13

Related Questions