By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE babynames
(`id` int, `category_id` int, `baby_name` varchar(11), `url_role` varchar(11))
;
INSERT INTO babynames
(`id`, `category_id`, `baby_name`, `url_role`)
VALUES
(1, 6, 'karthik', 'karthik'),
(2, 7, 'kalaivanan', 'kalaivanan'),
(3, 4, 'arun', 'arun'),
(4, 6, 'vijayakumar', 'vijayakumar'),
(5, 6, 'karthik', 'karthik'),
(6, 6, 'karthik', 'karthik'),
(7, 4, 'karthik', 'karthik'),
(9, 6, 'vijayakumar', 'vijayakumar'),
(8, 4, 'karthik', 'karthik')
SELECT * FROM babynames
| id | category_id | baby_name | url_role |
|---|---|---|---|
| 1 | 6 | karthik | karthik |
| 2 | 7 | kalaivanan | kalaivanan |
| 3 | 4 | arun | arun |
| 4 | 6 | vijayakumar | vijayakumar |
| 5 | 6 | karthik | karthik |
| 6 | 6 | karthik | karthik |
| 7 | 4 | karthik | karthik |
| 9 | 6 | vijayakumar | vijayakumar |
| 8 | 4 | karthik | karthik |
DELETE b
FROM babynames b
WHERE EXISTS (SELECT *
FROM (SELECT * FROM babynames) b1
WHERE b1.category_id = b.category_id
AND b1.baby_name = b.baby_name
AND b1.id < b.id)
SELECT * FROM babynames
| id | category_id | baby_name | url_role |
|---|---|---|---|
| 1 | 6 | karthik | karthik |
| 2 | 7 | kalaivanan | kalaivanan |
| 3 | 4 | arun | arun |
| 4 | 6 | vijayakumar | vijayakumar |
| 7 | 4 | karthik | karthik |
DELETE b
FROM babynames b
WHERE EXISTS (SELECT *
FROM babynames b1
WHERE b1.category_id = b.category_id
AND b1.baby_name = b.baby_name
AND b1.id < b.id)
You can't specify target table 'b' for update in FROM clause