•2:17 AM
Input Data
Query to display the duplicate values from the table
---------------------------------------------------
SELECT a.* FROM dbo.dept As [a]
INNER JOIN
(
SELECT dept_name, mgr_name FROM dbo.dept
GROUP BY dept_name, mgr_name
HAVING Count(*) >1
) As [b]
ON a.dept_name = b.dept_name
AND a.mgr_name = b.mgr_name
Output
---------
Query to delete the duplicate values from the table
--------------------------------------------------------
DELETE FROM dbo.dept
WHERE dbo.dept.dept_id IN
-- List 1 - all rows that have duplicates
(SELECT T1.dept_id
FROM dbo.dept AS T1
WHERE Exists (SELECT dept_name, mgr_name, Count(dept_id)
FROM dbo.dept
WHERE dbo.dept.dept_name = T1.dept_name
AND dbo.dept.mgr_name = T1.mgr_name
GROUP BY dbo.dept.dept_name, dbo.dept.mgr_name
HAVING Count(dbo.dept.dept_id) > 1))
AND dbo.dept.dept_id NOT IN
-- List 2 - one row from each set of duplicate
(SELECT Min(dept_id)
FROM dbo.dept AS T1
WHERE Exists (SELECT dept_name, mgr_name, Count(dept_id)
FROM dbo.dept
WHERE dbo.dept.dept_name = T1.dept_name
AND dbo.dept.mgr_name = T1.mgr_name
GROUP BY dbo.dept.dept_name, dbo.dept.mgr_name
HAVING Count(dbo.dept.dept_id) > 1)
GROUP BY dept_name, mgr_name)
Output
----------
3 comments:
retro jordans
nike kd 11
golden goose sneakers
ralph lauren uk
supreme clothing
louboutin
air max 90
moncler outlet
bape hoodie
yeezy boost 350
her comment is here blog this page aaa replica bags additional hints he said
replica bags sydney gucci replica handbags g2j30y4y78 replica bags hong kong imp source u6m30r6g92 bag replica high quality replica bags from china free shipping browse around this website r7n83z6z34 louis vuitton fake replica bags hong kong a9e28o6j19