Delete rows where NULL but NOT NULL also exists?

SQL Server Administration
2 
DELETE yt 
FROM
	YourTable yt
WHERE
	transfer_date IS NULL 
	AND EXISTS
    (
		SELECT *
		FROM
			YourTable yt2
		WHERE
			yt2.ref_id = yt.ref_id
			AND yt2.transfer_date IS NOT NULL
	);

If you want to see the rows that the above query will delete, run this before you run the above query

--DELETE yt 
SELECT *
FROM
	YourTable yt
WHERE
	transfer_date IS NULL 
	AND EXISTS
    (
		SELECT *
		FROM
			YourTable yt2
		WHERE
			yt2.ref_id = yt.ref_id
			AND yt2.transfer_date IS NOT NULL
	);
1 Like