Delete rows where NULL but NOT NULL also exists?

SQL Server Administration
1

I'm doing a test before applying to Apknite company, but I have a problem.
I have a simple table:

id transfer_date ref_id

I have 600'000 entries in it. On my first iteration it inserted entries, those that it could not retrieve - it inserted NULL entry for them, so I end up with

id transfer_date ref_id
515 NULL 123155

Then when I ran script 2nd time, it attempted to retrieve entries for NULL values only. So now I end up with a table that has values like:

id transfer_date ref_id
515 NULL 123155
123151 2019/08/16 123155

How can I delete ROWS that are NULL WHERE NON NULL entry exists for ref_Id?

This is on SQL Server 2008 R2.

Thanks in advance!

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