deleting duplicate rows

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mayanktalwar1988
Forum Contributor
Posts: 133
Joined: Wed Jul 08, 2009 2:44 am

deleting duplicate rows

Post 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
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: deleting duplicate rows

Post 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);
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Re: deleting duplicate rows

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: deleting duplicate rows

Post 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.
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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: deleting duplicate rows

Post 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 ....
There are 10 types of people in this world, those who understand binary and those who don't
mayanktalwar1988
Forum Contributor
Posts: 133
Joined: Wed Jul 08, 2009 2:44 am

Re: deleting duplicate rows

Post by mayanktalwar1988 »

the no of records are nearly 2 lakh
Post Reply