0

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.

1
  • Was there a reason you couldn't do this right in the Edit tab of the CP? You can find entries not assigned to a category then assign them all at once there... – Michael Boyink May 14 '15 at 13:49
2
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;
6
  • I get an SQL syntax error, if I move the SET before the WHERE then it works but it affects 0 rows. – KeironLowe May 14 '15 at 13:12
  • Ooops! Replace that WHERE with and AND ... I suck at MySQL too, I almost always have to rehearse MySQL answers before I get them right. I was on the bus to my office when I answered :) and put that SET back at the end, I updated my answer. – jrothafer May 14 '15 at 13:17
  • Haha definitely getting there! Different error this time: Error : Duplicate entry '12-88' for key 'PRIMARY'. I should mention 88 is the ID the category I need. – KeironLowe May 14 '15 at 14:20
  • Ahhh! I took a look at that table just now; You need to be doing an INSERT command, those rows don't exists! I'll update my answer yet again in a minute. – jrothafer May 14 '15 at 14:29
  • You sir, are a scholar and a gentleman. That has worked perfectly, thank you! – KeironLowe May 14 '15 at 16:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.