Thread

I’m trying to run this:

UPDATE exp_category_posts
SET cat_id = 425
WHERE cat_id IN (179,224,377…etc.)

to bulk update a set of entries to reassign them to a new category but am getting Duplicate entry ‘683-425’ for key ‘PRIMARY’.

When I look in the table I can see that there’s a row with entry_id 683 that already has a cat_id of 425 so I thought if I added AND cat_id != 425 to the query that would sort that out, but it hasn’t so not sure what’s going on.

I want to use a SQL query to do the updates rather than going through the CP because I have over a 1000 entries imported from Blogger that are assigned to single-entry categories and I want to consolidate the entries into larger, global categories.

This is because one entry may have more than one category. For example an entry might have a category of 179 and 224 so you could change one to category 425 and delete the other. You could use IGNORE on the update to ignore duplicate entries and then remove any that are duplicates.

UPDATE IGNORE exp_category_posts
SET cat_id = 425
WHERE cat_id IN (179,224,377…etc.);

DELETE FROM exp_category_posts
WHERE cat_id IN (179,224,377…etc.);

Yep, that did it. Thanks very much for your help. 😊