I want to eliminate duplicate names and surnames in a table for a certain event, in the query everything is perfect, but when I change the select to delete I get an error
QUERY (WORKING PERFECTLY):
WITH C AS
(
SELECT id,nombre,apellido,evento_id,
ROW_NUMBER() OVER (PARTITION BY
nombre,apellido
ORDER BY id) AS DUPLICADO
FROM usuario_evento
)
SELECT * FROM C
WHERE DUPLICADO > 1
AND evento_id = 10
when deleting (HERE IS THE ERROR)
WITH C AS
(
SELECT id,nombre,apellido,evento_id,
ROW_NUMBER() OVER (PARTITION BY
nombre,apellido
ORDER BY id) AS DUPLICADO
FROM usuario_evento
)
DELETE FROM C
WHERE DUPLICADO > 1
AND evento_id = 10
Error Code: 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 C WHERE DUPLICATE > 1
and event_id = 10' at line 9
I have mysql server installed, I suppose it is in the latest version. I don't use xampp or anything like that.
try it like this
join:
partition: