I had a SQL query that had to update various indexed unique fields in my database which went something like this:
UPDATE page SET path = REPLACE(path, 'junk', '') ON DUPLICATE KEY DELETE path
Obviously this isn’t a valid MySQL query. The work around is to use UPDATE IGNORE and then delete any rows after which contains the substring ‘junk’:
UPDATE IGNORE page SET path = REPLACE(path, 'junk', ''); DELETE FROM page WHERE path LIKE '%junk%'