SQLTeam.com | Weblogs | Forums

Removing Duplicates


#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