Delete All Data Found With Query

Question

I am working on a database project, and for sanity's sake I want to delete all of my problem data from the database while I test it's other features.

I currently have some queries written up to help me find my problem data sets like the code below:

SELECT Order_ID, Part_Number, COUNT(*)
FROM Temp_A
GROUP BY Order_ID, Part_Number
HAVING COUNT(*) > 1

Now I want to be able to delete all of the data I find with these query from my Temp Table. However putting the code in:

DELETE FROM Temp_A
WHERE IN (/*MY QUERY*/)

Doesn't seem to work, as I am getting a Syntax error. I've tried removing the IN, changing the WHERE to WITH, I am not sure exactly how to structure this query to get it to do what I want it to do.

score 2 · Accepted Answer · answered Jun 15 '18 at 13:36

One way would be using an INNER JOIN:

DELETE A
FROM Temp_A A
INNER JOIN (SELECT Order_ID, Part_Number, COUNT(*)
            FROM Temp_A
            GROUP BY Order_ID, Part_Number
            HAVING COUNT(*) > 1) B
    ON A.Order_ID = B.Order_ID
    AND A.Part_Number = B.Part_Number
;

If you are using SQL Server 2005+, then you can use a CTE:

WITH CTE AS
(
    SELECT  *,
            N = COUNT(*) OVER(PARTITION BY OrderID, Part_Number)
    FROM Temp_A
)
DELETE FROM CTE
WHERE N > 1
;

This worked for me, thank you. – Skitzafreak Jun 15 '18 at 14:45 — Skitzafreak, Jun 15 '18 at 14:45

Tom Baker · Answer 2 · 2018-06-15T13:51:50.073

0

can you delete by the Order_ID? if so you could technically try the following

DELETE FROM Temp_A
where Order_ID IN(
   SELECT Order_ID
   FROM Temp_A
   GROUP BY Order_ID, Part_Number
   HAVING COUNT(*) > 1)

This will select the same select list you included above, but only actually select the Order_ID, thus returning your list of Order_ID's to delete with

As pointed out however, this will only work if Order_ID is a primary key! eep, so not perfect i admit

edited Jun 15 '18 at 13:51

answered Jun 15 '18 at 13:39

Tom Baker

61
1
9

But what if there's a combination of `Order_Id` and `Part_Number` that appears just once, and the same `Order_Id` and another `Part_Number` appear more than once?....you'll delete every result for that `Order_Id` – Lamak Jun 15 '18 at 13:41
You're right :O i guess i'm taking a huge assumption that Order_ID is a non-duplicatable primary key. I guess my idea will only work in that scenario, i will update my question – Tom Baker Jun 15 '18 at 13:51

Delete All Data Found With Query

2 Answers2