Page 1 of 1
deleting duplicate rows
Posted: Sat Jul 31, 2010 1:30 am
by mayanktalwar1988
Code: Select all
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
Code: Select all
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
Re: deleting duplicate rows
Posted: Sun Aug 01, 2010 2:45 am
by JakeJ
Try INSERT instead of SELECT. But even still, you don't have the right syntax.
INSERT INTO table (field_1, field_2, field3) VALUES(1,2,3);
Re: deleting duplicate rows
Posted: Sun Aug 01, 2010 7:56 am
by superdezign
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.
Re: deleting duplicate rows
Posted: Sun Aug 01, 2010 8:14 am
by VladSun
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.
Re: deleting duplicate rows
Posted: Sun Aug 01, 2010 8:19 am
by VladSun
mayanktalwar1988 wrote:Code: Select all
create temp SELECT *
INTO temp
FROM downloads
GROUP BY downloadlink
HAVING COUNT(downloadlink) > 1
Code: Select all
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
Code: Select all
CREATE TEMPORARY new_table LIKE original_table SELECT ....
Re: deleting duplicate rows
Posted: Sun Aug 01, 2010 11:15 am
by mayanktalwar1988
the no of records are nearly 2 lakh