Link to home
Start Free TrialLog in
Avatar of r3nder
r3nderFlag 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
____________________________
Avatar of Leo Torres
Leo Torres
Flag of United States of America image

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

Open in new window

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

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"
Avatar of 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
< edited first comment after it was originally submitted, pls hit refresh >
ASKER CERTIFIED SOLUTION
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 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

Avatar of r3nder

ASKER

worked great thanks eghtbas