I'm doing a category system for my website, and I need to be able to insert a category into its table if it was not updated due to its non-existence.
I have the first part that updates all categories defined in the WHERE IN statement here:
UPDATE `cat` SET `posts` = posts +1 WHERE cat IN ('category1', 'category2', 'idonotexist1', 'idonotexist2')
How would I have SQL efficiently insert into the table any categories which could not be updated because they did not exist?
Just to be specific, I know how to do this when you are only doing one UPDATE, but with WHERE IN, its a bit more complicated for me. I'm looking for something that will insert idonotexit1 and idonotexist2 into the table, since it does not exist.
I will provide all information requested.
First, be sure that you have a unique index in your cat table:
alter table cat add unique index idx_cat(cat);
After that, for each category in your list, you can do something like:
insert into cat values('category1', 1)
on duplicate key update posts = posts + 1;