MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test (id INT, val INT);
INSERT INTO test VALUES
(1,1),(2,2),(3,3),(4,4),(4,4),(4,4),(5,7),(6,8),(7,9),(7,9),(8,11),(9,12);
SELECT * FROM test;


id
val


1
1

2
2

3
3

4
4

4
4

4
4

5
7

6
8

7
9

7
9

8
11

9
12


ALTER TABLE test ADD PRIMARY KEY (id);
Duplicate entry '4' for key 'test.PRIMARY'

CREATE TEMPORARY TABLE temptable LIKE test;
INSERT INTO temptable
    SELECT *
    FROM test
    GROUP BY id, val
    HAVING COUNT(*) > 1;
SELECT * FROM temptable;


id
val


4
4

7
9


DELETE test.*
    FROM test
    NATURAL JOIN temptable;
SELECT * FROM test;


id
val


1
1

2
2

3
3

5
7

6
8

8
11

9
12


INSERT INTO test 
    SELECT * FROM temptable;
ALTER TABLE test ADD PRIMARY KEY (id);
SELECT * FROM test;


id
val


1
1

2
2

3
3

4
4

5
7

6
8

7
9

8
11

9
12