database delete duplicates?

[gangsterwanster1]

How can i do a check in a database where if the row is the same it deletes it?

 

if

[database]

|example||name||date||registered||misc|

|test||test||test||test||test|

|example||name||date||registered||misc|

 

= duplicate then delete.

 

Final =

|example||name||date||registered||misc|

|test||test||test||test||test|

 

[gangsterwanster1]

I found this query from google;

 

SELECT *
FROM prices
WHERE id not in
	(SELECT max(id)
	FROM prices
                GROUP BY cat_no
        HAVING count(*) > 1)
&&
           id not in
	(SELECT id
	FROM prices
                GROUP BY cat_no
        HAVING count(*) = 1)

But when i insert it nothing happens, plus its only by one column 'cat_no'

 

ideas?

[gangsterwanster1]

any ideas?

[gangsterwanster1]

Been waiting all day for a response...

[seventheyejosh]

 

Did you try this?

[sasa]

ALTER IGNORE TABLE `table_name` ADD UNIQUE INDEX `xxx` (`field_1`, `field_2`, `field_3`, ...)