Question 91665 in Mysql Question posted in
The official MySQL documentation can be found here.

Delete multiple row in the table using WITH cluse 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!

Tags:

2

Answers


  1. 0

    Since the CTE is not updatable, you need to refer to the original table to delete rows using JOIN :

    WITH studentTable AS
(
   SELECT *, ROW_NUMBER() OVER(PARTITION BY studentID ORDER BY studentID) AS RowNumber
   FROM student_quiz
)
DELETE FROM student_quiz
USING student_quiz join studentTable on student_quiz.id = studentTable.id
WHERE studentTable.RowNumber > 1;

    Demo here

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search