Can’t remove duplicates from MariaDB 10

I’m having hard time removing duplicates from database. It’s MariaDB (protocol version: 10, 10.3.34-MariaDB Server). I need to remove rows where three columns are equal. I was trying to use WITH clause but database throws error that it can’t recognize ‘WITH’, so I focused on traditional way.

I need to remove rows where foreignId, column1 and column2 are equal.

I’m checking if there are duplicates like

SELECT foreignId, column1, column2, COUNT(*)
FROM table1
GROUP BY foreignId, column1, column2
HAVING COUNT(*) > 1

Trying to remove duplicates…

DELETE table1
FROM table1
INNER JOIN (
  SELECT
    p.id,
    p.foreignId,
    p.column1,
    p.column2,
    ROW_NUMBER() OVER (
      PARTITION BY 
        p.column1, 
        p.column2, 
        p.foreignId
      ORDER BY
        p.foreignId,
        p.column2,
        p.column1
    ) AS row_number
  FROM table1 p
  GROUP BY p.foreignId, p.column1, p.column2
) dup
ON table1.column1 = dup.column1
WHERE dup.row_number > 1;

I was modifying this code alot but still can’t make it work as intended… What am I doing wrong?

Answer

Assuming you have a unique column like id, you can do following:

DELETE FROM table1 WHERE ID NOT IN 
(SELECT x.id FROM
(SELECT MAX(id) id, MAX(foreignId) foreignId, 
MAX(column1) column1, MAX(column2) column2
FROM table1 
WHERE ttimestamp IN (SELECT MAX(ttimestamp) FROM table1 
GROUP BY foreignID, column1, column2)
GROUP BY foreignId, column1, column2)x); 

Please see the working example here: db<>fiddle