My student table has data:
------------------------
id | name | student_id
------------------------
1 | john | 8899
2 | john | 9091
3 | mary | 9823
4 | john | 9091
------------------------
I need to delete duplicate rows which has same value of both name and student_id column.
Example from the data above id 2 and 4 is duplicate , and I need to delete 4 which is the later entry.
I tried to
ALTER IGNORE TABLE `student` ADD UNIQUE( `name`, `student_id`);
but my sql version has already remove alter ignore table
and
WITH cte AS (
SELECT name, student_id, COUNT(*) AS row_num FROM student GROUP BY name, student_id
HAVING COUNT(*) > 1
)
DELETE FROM cte
WHERE row_num > 1;
but my sql version doesn't support CTE. Are there other ways that I can remove the duplicate rows ?
