mysql - Can I update a set of rows only if the updated row does not already exist? -

i have database 2 tables, url , tweet. url has following structure:

 id | expanded_url | display_url | url --------------------------------------- 

there many-to-many relationship between url , tweet, there join table (urls_tweets) looks this:

 id | tweet_id | url_id ------------------------ 

the problem there duplicate entries in url table -- unique indexes had not been used properly, , trying clear out duplicates can create those. i've run query find duplicate entries in url table, , removing them easy enough.

the problem need update urls_tweets point canonical record, since duplicates being deleted. of course, in urls_tweets, tweet_id , url_id must unique together. running simple query following fails because create records violate uniqueness constraints on urls_tweets:

update urls_tweets set url_id = <primary url record id> url_id in (<duplicate url record ids>); 

is there way update, or create or delete or whatever have do, have records in urls_tweets point correct url record without having duplicate (tweet_id, url_id) pairs?

create table url_id_remap contains url_ids duplicates, , preferred canonical url_id. populate table deletions.

then update urls_tweets table using join:

update urls_tweets ut join url_id_remap r on ut.url_id = r.duplicate_url_id set ut.url_id = r.canonical_url_id; 

the join naturally limits rows need changed, because won't find entry in url_id_remap satisfies join unless you've inserted row url_id.