This is a pretty specific case scenario so I’ve written this post more for my own future self in case I need it again 🙂 I have one site that is programmatically adding products in old OpenCart and after debugging the script after a PHP update, I realised I had duplicates in the URL alias table (which meant the products gave a ‘Product not found’ error. This is how I cleared the table of duplicates.
DELETE t1 FROM oceli_url_alias t1
INNER JOIN oceli_url_alias t2
t1.url_alias_id < t2.url_alias_id AND
t1.keyword = t2.keyword;