Search code examples
sqlmysqlmariadbsql-delete

Delete multiple row in the table using WITH and Partition in MySQL

I'm trying to delete multiple duplicate rows/data, but the problem is MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DELETE FROM studentTable WHERE RowNumber > 1' at line 6

Here's my Code:

WITH studentTable AS
(
   SELECT *, ROW_NUMBER() OVER(PARTITION BY studentID ORDER BY studentID) AS RowNumber
   FROM student_quiz
)
DELETE FROM studentTable WHERE RowNumber > 1;

Here's the result of Select Version: enter image description here

I found this code in the youtube and this video was uploaded 11 year ago, So I'm not sure if this code still working in the current version of MySQL. I hope you can help me with this problem. Thank you!

Solution

  • Neither MySQL nor MariaDB support updatable CTEs.

    If your table has a primary key, say pk, then I would recommend a simple self-join:

    delete q
from student_quiz q
inner join (select id, min(pk) pk from student_quiz group by id) q1
    on q1.id = q.id and q1.pk < q.pk

    Basically, for each id this retains the row with the smallest pk and delete the others, if any.