phpmyadmin Question posted in
The official documentation can be found here.

I want to delete all the duplicates row in a MySQL database but it freezes – Phpmyadmin

whenever I try a deleting operation on my phpmyAdmin MySQL database it freezes saying ‘Error 504’ once I run the command.

The operation is like the following:

DELETE t1 FROM downloads t1
        INNER JOIN
    downloads t2
WHERE
    t1.ID < t2.ID AND t2.download = t2.download AND t2.place = t2.place AND t2.IP = t2.IP AND t2.data = t2.data

I have a table with dozens thousands entries and I just want to keep those with the highest ID and removing the otherd having lowest ID+same place+same IP+same data.

Tags:

2

Answers


  1. 0

    You can try below – your joining alias is not right you are trying t2.download = t2.download and so on which is wrong

    DELETE t1
FROM downloads t1
INNER JOIN downloads t2
WHERE t1.ID < t2.ID
  AND t1.download = t2.download
  AND t1.place = t2.place
  AND t1.IP = t2.IP
  AND t1.data = t2.data
    Login or Signup to reply.
  2. 0

    As per comments, if you can take the system off-line for a couple of minutes, then I’d do this:

    -- After making a backup of downloads...

CREATE TABLE new_downloads AS
SELECT a.* 
  FROM downloads a
  JOIN 
     ( SELECT MAX(id) id
         FROM downloads 
        GROUP 
           BY download
            , place
            , IP
            , data 
     ) b
    ON b.id = a.id;

ALTER TABLE new_downloads ADD PRIMARY KEY (ID);
-- add any other indexes as required

DROP TABLE downloads;

ALTER TABLE new_downloads RENAME TO downloads;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search