1DELETE FROM dups a USING (
2 SELECT MIN(ctid) as ctid, key
3 FROM dups
4 GROUP BY key HAVING COUNT(*) > 1
5 ) b
6 WHERE a.key = b.key
7 AND a.ctid <> b.ctid
1WITH cte AS (
2 SELECT
3 contact_id,
4 first_name,
5 last_name,
6 email,
7 ROW_NUMBER() OVER (
8 PARTITION BY
9 first_name,
10 last_name,
11 email
12 ORDER BY
13 first_name,
14 last_name,
15 email
16 ) row_num
17 FROM
18 sales.contacts
19)
20DELETE FROM cte
21WHERE row_num > 1;
22
1-- Oracle
2DELETE films
3WHERE rowid NOT IN (
4 SELECT min(rowid)
5 FROM films
6 GROUP BY title, uk_release_date
7);
1# Step 1: Copy distinct values to temporary table
2CREATE TEMPORARY TABLE tmp_user (
3 SELECT id, name
4 FROM user
5 GROUP BY name
6);
7
8# Step 2: Remove all rows from original table
9DELETE FROM user;
10
11# Step 3: Remove all rows from original table
12INSERT INTO user (SELECT * FROM tmp_user);
13
14# Step 4: Remove temporary table
15DROP TABLE tmp_user;
16
1--ID should be primary key
2
3--get duplicate records using RANK
4SELECT E.ID,
5 E.firstname,
6 E.lastname,
7 E.country,
8 T.rank
9FROM [SampleDB].[dbo].[Employee] E
10 INNER JOIN
11(
12 SELECT *,
13 RANK() OVER(PARTITION BY firstname,
14 lastname,
15 country
16 ORDER BY id) rank
17 FROM [SampleDB].[dbo].[Employee]
18) T ON E.ID = t.ID;
19
20--delete duplications
21DELETE E
22 FROM [SampleDB].[dbo].[Employee] E
23 INNER JOIN
24 (
25 SELECT *,
26 RANK() OVER(PARTITION BY firstname,
27 lastname,
28 country
29 ORDER BY id) rank
30 FROM [SampleDB].[dbo].[Employee]
31 ) T ON E.ID = t.ID
32 WHERE rank > 1;