create temp SELECT *
INTO temp
FROM downloads
GROUP BY downloadlink
HAVING COUNT(downloadlink) > 1
DELETE downloads
WHERE id
IN (SELECT id
FROM temp)
INSERT downloads
SELECT *
FROM temp
DROP TABLE temp
i used the above query to delete duplicates from downloads but it didnt worked
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'temp SELECT * INTO temp FROM downloads ' at line 1
first i selected the rows where downloadlink column is duplicate then thrown the duplicate ones in other table then used delete to delete all the rows in downloads that occured in temp tables..and then after that pushed the rows from temp to downloads to have that one copy from duplicates..and then drop temp..not working
You could also just allow your database to remain as is and just use GROUP BY to avoid selecting duplicates. Removing this data likely won't affect much.
superdezign wrote:You could also just allow your database to remain as is and just use GROUP BY to avoid selecting duplicates. Removing this data likely won't affect much.
It won't work straight if there is an OUTER JOIN clause. Also, we don't know the number of records - it may be a huge one, and GROUP BY is relatively slow.
Last edited by VladSun on Sun Aug 01, 2010 3:29 pm, edited 1 time in total.
There are 10 types of people in this world, those who understand binary and those who don't
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'temp SELECT * INTO temp FROM downloads ' at line 1