0

In my table are a lot of duplicate rows. I have a SELECT that provides the data I want to delete but how can I do this? Whatever I try I get an error. 

SELECT *
  FROM tablename t
 WHERE NOT EXISTS (         
              SELECT 1
                FROM tablename t2
               WHERE t2.column = t.column
              HAVING COUNT(*) = 1
                     )
Burhan Khalid
  • 169,990
  • 18
  • 245
  • 284
user2826395
  • 17
  • 4
  • 1
    change `select *` to `delete`... – Ben Oct 06 '13 at 11:17
  • P.S., if you have duplicates then you need to start learning about [unique and primary keys](http://en.wikipedia.org/wiki/Unique_key). – Ben Oct 06 '13 at 11:20
  • yes i tryed this but it shows error: #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 't WHERE NOT EXISTS ( SELECT 1 FROM norma' at line 2 btw. kollation is utf8mb4_bin – user2826395 Oct 06 '13 at 11:29
  • What version of MySQL? – Burhan Khalid Oct 06 '13 at 11:37

1 Answers1

0

Just need to change 'SELECT *' to 'DELETE'

DELETE 
FROM tablename t
WHERE NOT EXISTS (         
              SELECT 1
              FROM tablename t2
              WHERE t2.column = t.column
              HAVING COUNT(*) = 1
              )
Ofir
  • 5,049
  • 5
  • 36
  • 61
  • yes i tryed this but it shows error: #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 't WHERE NOT EXISTS ( SELECT 1 FROM norma' at line 2 btw. kollation is utf8mb4_bin – user2826395 Oct 06 '13 at 11:24