Removing Duplicates

Transact-SQL
1

I have a table with duplicates but how do I remove only the duplicates with the null values? Here is what I have:

If anyone can point me in the right direction, thank you!

2

Can you please post your data in a consumable format? Preferably:

  1. Create table statement
  2. Insert into statement
3

Do you want to remove ID 8756? Or keep that row? All columns except ID are NULL but that ID only appears in one row.

4

What if there is not a duplicate but and ID with all NULLs?

You might use something like COALESCE(Name, Month, Date) IS NULL

5

Keep ID 8756. Remove only those IDs that got duplicated and have all NULL values.

6 
DELETE FROM tn
FROM table_name tn
WHERE tn.Name IS NULL AND tn.Month IS NULL AND tn.Date IS NULL
  AND EXISTS(SELECT 1 FROM table_name tn2 WHERE tn2.ID = tn.ID 
             AND (tn2.Name IS NOT NULL OR tn2.Month IS NOT NULL OR tn2.Date IS NOT NULL))
7

Thank you! This looks like it could work however I got an error:
"A FROM clause is currently not supported in a DELETE statement."

I believe I'm using an APS machine which uses DSQL. I can't find the translation for this.

8

Remove the FROM after the DELETE

9

Sorry, I get spoiled by the MS extensions!

DELETE FROM table_name
WHERE table_name.Name IS NULL AND table_name.Month IS NULL AND table_name.Date IS NULL
  AND EXISTS(SELECT 1 FROM table_name tn2 WHERE tn2.ID = table_name.ID 
             AND (tn2.Name IS NOT NULL OR tn2.Month IS NOT NULL OR tn2.Date IS NOT NULL))
10

That took out all the IDs that had Null values, duplicate or not.

11

Hmm, odd, don't see how, but I couldn't test it since I didn't have sample data. A picture of data is useless for trying to actually test :-).

12

This at least gives me some direction. Thank you!

13

Seems OK to me. You can even re-run the DELETE part multiple times and the 8756 row is left alone:

CREATE TABLE table_name (
    ID int,
    Name varchar(30) NULL,
    Month varchar(30) NULL,
    Date varchar(30) NULL
    )
insert into table_name values(4434,null,null,null)
insert into table_name values(4434,'Bob','November','11012015')
insert into table_name values(8756,null,null,null)
insert into table_name values(5630,'Steve','December','12012015')
insert into table_name values(5630,null,null,null)
insert into table_name values(2538,'Sara','October','10042014')
select * from table_name
DELETE FROM table_name
WHERE table_name.Name IS NULL AND table_name.Month IS NULL AND table_name.Date IS NULL
  AND EXISTS(SELECT 1 FROM table_name tn2 WHERE tn2.ID = table_name.ID 
             AND (tn2.Name IS NOT NULL OR tn2.Month IS NOT NULL OR tn2.Date IS NOT NULL))
select * from table_name
DELETE FROM table_name
WHERE table_name.Name IS NULL AND table_name.Month IS NULL AND table_name.Date IS NULL
  AND EXISTS(SELECT 1 FROM table_name tn2 WHERE tn2.ID = table_name.ID 
             AND (tn2.Name IS NOT NULL OR tn2.Month IS NOT NULL OR tn2.Date IS NOT NULL))
select * from table_name
14

take a look at this link hope this will help you out: http://stackoverflow.com/questions/16546537/remove-duplicates-with-nulls