Avatar of r3nder
r3nder
Flag for United States of America asked on

delete duplicates with a null value

I have a query that is pretty long - at the end before I do the select I want to delete duplicate serial numbers with no jobtype
how can I do this
Example data
type|SN|qty|job type
____________________________
TN        87       1       
____________________________
TN        87       1      surefire
____________________________
TN        88       1       
____________________________
TN        88       1      surefire
____________________________
Microsoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
r3nder

8/22/2022 - Mon
Leo Torres

did you try something to the effect
delete from table 
where isNULL(Type,'') = ''

Open in new window

Jim Horn

By 'delete' do you mean not show them in your query, or actually delete rows?

Probably a more efficient way to pull this off, but borrowing from my article at SQL Server Delete Duplicate Rows Solutions, the 'DELETE #1' section

Copy-paste the below T-SQL into your SSMS, execute to verify it does what you want, then modify to fit your needs
CREATE TABLE #tmp (type char(2), SN int, qty int, jobtype varchar(10))

INSERT INTO #tmp (type, SN, qty, jobtype) 
VALUES 
	('TN', 87, 1, NULL), ('TN', 87, 1, 'surefire'), ('TN', 87, 1, NULL),
	('TN', 88, 1, NULL), ('TN', 88, 1, 'surefire'),
	('TN', 89, 1, NULL)

;with a as (
   SELECT type, SN, qty, COUNT(qty) as row_count
   FROM #tmp
   GROUP BY type, SN, qty
   HAVING COUNT(qty) > 1
)
DELETE 
FROM #tmp
FROM #tmp
	JOIN a ON a.type = #tmp.type AND a.SN = #tmp.SN AND a.qty = #tmp.qty 
WHERE #tmp.jobtype IS NULL

SELECT * FROM #tmp

Open in new window

Guy Hengel [angelIII / a3]

as we are speaking about "duplicates", I would rather do this:
delete t1
from table  t1
where isNULL(t1.Type,'') = ''
 and exists ( select null from table t2 where t2.type = t1.type and T2.SN = T1.SN and T2.qty = T1.qty
   and isNULL(t2.Type,'') <> ''
  )

Open in new window


of course, it depends on your definition of "dupliates"
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
r3nder

ASKER
That would work but it would delete data that was not a duplicate
revised Example data:
TN      51      1       
____________________________
TN      66      1       
____________________________
TN      70      1       
____________________________
TN      87      1       
____________________________
TN      87      1      surefire
____________________________
TN      88      1       
____________________________
TN      88      1      surefire
Jim Horn

< edited first comment after it was originally submitted, pls hit refresh >
ASKER CERTIFIED SOLUTION
Mike Eghtebas

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
r3nder

ASKER
@Jim
here is the query I used and the output after
TN      47      1      surefire
____________________________
TN      49      1       
____________________________
TN      49      1      surefire
____________________________
TN      51      1       
____________________________
TN      66      1       
____________________________
TN      70      1       
____________________________
TN      87      1       
____________________________
TN      87      1      surefire
____________________________
TN      88      1       
____________________________
TN      88      1      wildfire
____________________________
;with a as (
   SELECT tooltype,jobtype, serialnumber, qty, COUNT(qty) as row_count
   FROM temp_mytools WHERE tooltype = 'TN'
   GROUP BY jobtype, serialnumber, qty,tooltype
   HAVING COUNT(qty) > 1
)
DELETE 
FROM temp_mytools
FROM temp_mytools
	JOIN a ON a.jobtype = temp_mytools.jobtype AND a.serialnumber = temp_mytools.serialnumber AND a.qty = temp_mytools.qty and a.tooltype = temp_mytools.tooltype 
WHERE temp_mytools.jobtype IS NULL

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
r3nder

ASKER
worked great thanks eghtbas