Sql Exists on same table with sub query deletes all records?-CodePudding

QUERY

Delete from Image 
where Exists
(
  SELECT x.ImageId, ParentId, occurrences 
  from Image li 
  inner join 
  (
    SELECT  
      ImageId,    
      COUNT(*) occurrences
    FROM Image 
    GROUP BY ImageId
    HAVING COUNT(*) > 1
  ) x on li.ImageId = x.ImageId
)

DATA

ImageId   ParentId
a         1
a         2
b         3
c         4

EXPECTED REMAINING RESULT

ImageId   ParentId
b         3
c         4

The query in the Exists function returns the first two records with same ImageId a.

When I run the whole Delete query not ONLY these two records are deleted, but the whole table data is wiped!???

I found this SO link: Delete row from table where match exists in second table

where a user comments about this tricky situation, but the comment does not help me as the solution seems open to me.

How do I have to fix the delete query?

CodePudding user response：

Your subquery is not correlated, so it is simply deleting every row, as long as there is at least one row in the unrelated subquery.

The join is not necessary anyway, you just need to correlate it.

DELETE FROM Image 
WHERE EXISTS (SELECT 1
    FROM Image li
    WHERE li.ImageId = Image.ImageId
    HAVING COUNT(*) > 1
);

However this is probably more efficient with a window function

DELETE FROM li
FROM (
    SELECT *,
      Count = COUNT(*) OVER (PARTITION BY li.ImageId)
    FROM Image li
) li
WHERE li.Count > 1;

Note how the derived table is deleted from directly, no joins are necessary