delete duplicates with a null value

r3nder
r3nder used Ask the Experts™
on
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
____________________________
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Leo TorresSQL Developer

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

Open in new window

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
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"
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
r3nder

Author

Commented:
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 HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
< edited first comment after it was originally submitted, pls hit refresh >
Database and Application Developer
Commented:
This shows records to be deleted. verify if this is what you want to be deleted?
Select *  
from (Select rank() over(Partition by [type],SN,qty order by jobtype desc) as rnk
,[type],SN,qty,jobtype
from table1) As t
Where rnk=2

Open in new window


job type is changed to jobtype

if so then run: 
;With d 
as
(
Select *  
from (Select rank() over(Partition by [type],SN,qty order by jobtype desc) as rn
,[type],SN,qty,jobtype
from table1) As t
Where rn=2 
)
delete from d;

Open in new window



Mike
r3nder

Author

Commented:
@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

r3nder

Author

Commented:
worked great thanks eghtbas

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial