mySQL query not running delete after Row_Number function as the primary key no longer exists.(i have the primary key column in the new table)

Question

WITH idpublisher AS
(
   SELECT *, ROW_NUMBER()OVER(PARTITION BY publisher ORDER BY publisher)
     AS RowNumber
   FROM idpublisher
)
delete FROM idpublisher WHERE RowNumber > 1

Error Code: 1288. The target table idpublisher of the DELETE is not updatable 0.000 sec

i have my table idpublisher as updatable but when the query is run on it the new table is not updateable so it does not run the delete method — Muhammad Saleh, Jun 29 '22 at 13:06
@MuhammadSaleh if you have an id autoincrement on the table a simple self join would do the trick. Check the fiddle https://www.db-fiddle.com/f/b2sXP7rPxAFEUJ5QHJ9w4v/2 — Ergest Basha, Jun 29 '22 at 13:25
Please clarify via edits, not comments. Please delete/flag obsolete comments. PS [mre] [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) — philipxy, Jun 29 '22 at 17:14

score 0 · Accepted Answer · answered Jun 29 '22 at 13:17

you can do it like this, as your code isn't valid

I assume you have an auto_increnet that has a column name of id

Still, this is very dangerous, so it should be tested on a sample database or dbfidlle

WITH CTE AS
(
   SELECT *, ROW_NUMBER()OVER(PARTITION BY publisher ORDER BY publisher)
     AS RowNumber
   FROM idpublisher
)
delete FROM idpublisher WHERE id IN (SELECT id FROM CTE WHERE RowNumber > 1)

Or use a INNER JOIN

WITH CTE AS
(
   SELECT *, ROW_NUMBER()OVER(PARTITION BY publisher ORDER BY publisher)
     AS RowNumber
   FROM idpublisher
)
delete idpublisher FROM idpublisher JOIN CTE ON idpublisher.id = CTE.id WHERE  CTE.RowNumber > 1

Thanku the first code solved my problem. – Muhammad Saleh Jun 29 '22 at 16:49 — Muhammad Saleh, Jun 29 '22 at 16:49

mySQL query not running delete after Row_Number function as the primary key no longer exists.(i have the primary key column in the new table)

1 Answers1