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