[SOLUTION] delete duplicates with a null value

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
____________________________

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

@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

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 8

Leo TorresSQL DeveloperCommented: 2015-06-16

did you try something to the effect

delete from table 
where isNULL(Type,'') = ''

LVL 67

Jim HornMicrosoft SQL Server Data DudeCommented: 2015-06-16

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

LVL 143

Guy Hengel [angelIII / a3]Billing EngineerCommented: 2015-06-16

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,'') <> ''
  )

of course, it depends on your definition of "dupliates"

LVL 6

r3nderAuthor Commented: 2015-06-16

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 >

LVL 34

Mike EghtebasDatabase and Application DeveloperCommented: 2015-06-16

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

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;

Mike

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

worked great thanks eghtbas

delete duplicates with a null value

Who is Participating?