CODEWITHSUNDEEP
sqlsql-server
facVV
facVV

Reputation: 419

How to delete all the rows where each row's id is equal to another table's id (SQL Server)

mainGroup (datasetID greater than or equal to 1788)

dataGroup' name is the bold text.

datasetID | dataGroup                   | dataDesc
----------+-----------------------------+----------
1790      | **dGroup1** note xxxx       | desc1
1789      | **dGroup1** late notice ..  | desc2
1788      | **dGroup1** jack ...        | desc3

ex) dGroup1

dGroup1_ID | create_date | datasetID
-----------+-------------+-------
379        | 01-01-01    | 1790
378        | 01-01-01    | 1789
377        | 01-01-01    | 1788

I want to remove the tows in dGroup1/dGroup2/dGroup3 table where its datasetID is equal to corresponding datasetID in the mainGroup table, where datasetID in the mainGroup is greater than equal to 1788. (ex. 1790 for dGroup1, 1789 for dGroup2, etc.)

Instead of doing individually of deletion, is there any way to perform all at one?

Upvotes: 0

Views: 69

Answers (1)

Andres Silva
Andres Silva

Reputation: 892

So, you would like to delete records on tables dGroup1, dGroup2 & dGroup3 depending on the contents of table mainGroup.

For dGroup1, it looks like this:

DELETE t1
FROM dGroup1 t1
    INNER JOIN mainGroup t2
        ON t1.datasetID = t2.datasetID
        AND t2.datasetID >= 1788

You could repeat this for tables dGroup2 and dGroup3.

Upvotes: 1

Related Questions