I am using SQL Server 2008.

I have a SELECT query as follows:

SELECT 
    Apples.ID, COUNT(Pips.Apples_ID) 
FROM 
    Apples 
LEFT JOIN 
    Table_B tb ON Apples.ID = tb.Apples_ID
LEFT JOIN 
    Table_C tc ON tb.xID = tc.xID
LEFT JOIN 
    Pips p ON tb.Apples_ID = p.Apples_ID
WHERE 
    tc.X IS NULL
GROUP BY 
    Apples.ID

The tables are:

• Apples which has a unique entry (ID) for each Apple.
• Pips which can have dozens of pips belonging to 1 Apple
• Table_B and Table_C are mapping tables to refine the search

I need to group the results because I do not want an Apples result for each and every Pip that apples can have. The SELECT statement works and returns a list of unique Apple IDs

I now want to DELETE these Apples. I changed my statement to:

DELETE Apples 
FROM Apples 
LEFT JOIN Table_B tb ON Apples.ID = tb.Apples_ID
LEFT JOIN Table_C tc ON tb.xID = tc.xID
LEFT JOIN Pips p ON tb.Apples_ID = p.Apples_ID
WHERE tc.X IS NULL
GROUP BY Apples.ID

but got a syntax error on the GROUP BY.

I tried:

DELETE x 
FROM
    (SELECT Apples.ID 
     FROM Apples
     LEFT JOIN Table_B tb ON Apples.ID = tb.Apples_ID
     LEFT JOIN Table_C tc ON tb.xID = tc.xID
     LEFT JOIN Pips p ON tb.Apples_ID = p.Apples_ID
     WHERE tc.X IS NULL
     GROUP BY Apples.ID) x;

But I got an error:

View or function not updatable because the modification affects multiple base tables

How can I delete these rows I have identified in the SELECT, without using a temporary table or script?

CodePudding user response：

As others have pointed out, the sub-query approach can be adapted to work by using an IN ( ... ) clause on a normal single-table delete. This is the simplest way of adapting any select statement to a delete:

DELETE FROM Apples
WHERE ID IN (
   -- Sub-query selecting a single column of ID values
)

The sub-query can then be as complex as you like, using GROUP BY, HAVING, etc, as long as it only has one column in the SELECT list.

In your specific case, however, there is no need:

• You have no HAVING clause, so the COUNT() doesn't change the rows to delete
• The LEFT JOIN to the Pips table has no effect on the result other than the COUNT()
• Mentioning the same row twice in a DELETE has no effect, so eliminating duplicates is unnecessary

You can therefore simplify this particular case to:

DELETE Apples 
FROM Apples 
LEFT JOIN Table_B tb ON Apples.ID = tb.Apples_ID
LEFT JOIN Table_C tc ON tb.xID = tc.xID
WHERE tc.X IS NULL

CodePudding user response：

DELETE FROM Apples WHERE ID in 
(
SELECT a.ID FROM Apples a
LEFT JOIN Table_B tb ON a.ID = tb.a
LEFT JOIN Table_C tc ON tb.xID = tc.xID
LEFT JOIN Pips p ON tb.Apples_ID = p.a
WHERE tc.X IS NULL
GROUP BY a.ID
) as q

CodePudding user response：

Are you trying to achieve this:

DELETE FROM APPLES WHERE ID IN
(
SELECT Apples.ID FROM Apples
LEFT JOIN Table_B tb ON Apples.ID = tb.Apples_ID
LEFT JOIN Table_C tc ON tb.xID = tc.xID
LEFT JOIN Pips p ON tb.Apples_ID = p.Apples_ID
WHERE tc.X IS NULL
GROUP BY Apples.ID
) x;

CodePudding user response：

The only thing that has a role in the query is tc.X is null. It can be null if there is no match or there is a match but the field X is null:

delete from Apples
where AppleId in
(
SELECT Apples.ID FROM Apples 
  LEFT JOIN Table_b tb ON tApples.ID = tb.Apples_ID
  LEFT JOIN Table_C tc ON tb.xID = tc.xID
  WHERE tc.X IS NULL
);