Delete if no row matches condition in a table

Let's say I have a SQL table with the following rows:

Name: Null, Id: 15
Name: 'MyName', Id: 15

I want to delete all the rows with Name = NULL but ONLY IF rows with column Name IS NOT NULL don't exist in the table. So in this case it shouldn't delete any row. However, if it finds:

Name: Null, Id: 15
Name: 'Null', Id: 15

it should delete both rows.

I have tried this query but it doesn't work:

DELETE FROM MyTable
WHERE Name = NULL 
AND NOT EXISTS 
(SELECT 1 FROM MyTable a 
LEFT JOIN ...
LEFT JOIN ... 
WHERE Name IS NOT NULL)

CodePudding user response：

You can use an EXISTS

DELETE FROM MyTable
WHERE Name IS NULL
  AND NOT EXISTS (SELECT 1
    FROM MyTable t2
    WHERE t2.Name IS NOT NULL)

CodePudding user response：

Try like This

   DELETE FROM 
    MyTable
    WHERE  
    
    Name like '%NULL%' OR  
    Name like '%Null%'  OR  
    Name like '%null%'