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