0

I have this query:

SELECT url_rewrite_id, COUNT(url_rewrite_id) FROM catalog_url_rewrite_product_category GROUP BY url_rewrite_id HAVING COUNT(url_rewrite_id) > 1;

Which returns some duplicate entries in my database. All entries are COUNT 2:

enter image description here How can I combine to this the DELETE function so I can delete only 1 entry of each from my table?

I tried with:

DELETE url_rewrite_id, COUNT(url_rewrite_id) FROM catalog_url_rewrite_product_category GROUP BY url_rewrite_id HAVING COUNT(url_rewrite_id) > 1;

But I get error in mysql syntax:

Unexpected keyword. (near "GROUP BY" at position 87)
Unrecognized statement type. (near "GROUP BY" at position 87)

I also tried :

DELETE n1 FROM catalog_url_rewrite_product_category n1, catalog_url_rewrite_product_category n2 WHERE n1.url_rewrite_id > 1 AND n1.url_rewrite_id = n2.url_rewrite_id;

but this returns no entries.

Then tried:

DELETE n1 FROM catalog_url_rewrite_product_category n1, catalog_url_rewrite_product_category n2 WHERE n1.url_rewrite_id = n2.url_rewrite_id;

but this deletes all data from table

I can't figure out why is not working.

Any help please?

G. G.
  • 279
  • 1
  • 4
  • 14
  • If your query worked, it would delete ALL copies of the duplicates. Don't you want to leave one of them? – Barmar Aug 22 '21 at 18:12
  • @Barmar my query worked. Returned all entries and in COUNT column I see number 2. I need to remove only 1 of them. I have updated my question – G. G. Aug 22 '21 at 18:15
  • Your DELETE query didn't work. But since there's nothing in it that says to keep one, it would have deleted all copies if it did what you expected. – Barmar Aug 22 '21 at 18:17
  • My DELETE returns mysql error. The SELECT works fine – G. G. Aug 22 '21 at 18:18
  • I know. I was referring to the DELETE query when I said "if your query worked". It didn't work, it returned an error. – Barmar Aug 22 '21 at 18:19
  • @Barmar I am sorry. Didn't understand you. Can you help me to fix the right syntax? – G. G. Aug 22 '21 at 18:20
  • I already did. Don't you see the duplicate question I linked to above? – Barmar Aug 22 '21 at 18:23
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/236297/discussion-between-g-g-and-barmar). – G. G. Aug 22 '21 at 18:27

0 Answers0