Reassign uncategorised entries

Question

I need to reassign all uncategorised entries into a certain category, I've managed to create an SQL query which selects all the entries for that channel which aren't assigned to any categories, but I'm getting a bit stuck when it comes to updating the exp_category_posts table. SQL isn't my strongest area so help would be appreciated.

This is my SQL for selecting the entries:

SELECT exp_channel_data.entry_id FROM exp_channel_data LEFT JOIN exp_category_posts ON exp_channel_data.entry_id = exp_category_posts.entry_id WHERE exp_category_posts.entry_id IS NULL AND channel_id = 5;

So the exp_category_posts has two columns, entry_id and cat_id, I need to pull in the entry_id from the exp_channel_data table and hardcode the cat_id column to a certain value.

Answer 1

UPDATE exp_category_posts
    LEFT JOIN exp_channel_data
    ON exp_channel_data.entry_id = exp_category_posts.entry_id
    AND exp_category_posts.entry_id IS NULL 
    AND exp_channel_data.channel_id = 5
SET exp_category_posts.cat_id = 123;

Please backup those two tables before running any update queries, just in case. Also make sure you know how to restore said backups before proceeding.

Replace 123 with the category id you want to set the uncategorized entries to.

EDIT

Here are some queries for you.

This one will insert rows that don't exist in your exp_category_posts table and should not error out:

INSERT INTO exp_category_posts (entry_id, cat_id)
    SELECT exp_channel_data.entry_id, @cat_id := 88
    FROM exp_channel_data
    LEFT JOIN exp_category_posts
    ON exp_channel_data.entry_id = exp_category_posts.entry_id
    WHERE exp_category_posts.entry_id IS NULL AND exp_channel_data.channel_id = 5
    AND exp_channel_data.entry_id NOT IN (select entry_id from exp_category_posts);

and this one will update rows that already exists (but have cat_id = 0, which is null for this table) to have your cat_id = 88:

UPDATE exp_category_posts
SET exp_category_posts.cat_id = 88
WHERE exp_category_posts.cat_id = 0;