confused by insert on duplicate key update

Question

I'm trying to implement a 3 table item tagging system

Table: Item
Columns: ItemID (primary, auto_increment), Title, Content

Table: Tag
Columns: TagID (primary, auto_increment), Title, TagCount

Table: ItemTag
Columns: ItemID, TagID

Each Title of a tag is unique.
I would like to implement an efficient way of inserting to the Tag table:
1. Check if Title exists
2. If it exists, update TagCount+1
3. Else if it doesn't exist, insert

I looked into INSERT ON DUPLICATE KEY UPDATE, but I'm confused if it can be applied because my key is the TagID and not the Title. Any help is appreciated, thank you.

Answer 1

I'm assuming you are trying to do the insert into the table "Tag".

You need to modify the table and add a unique index on the title field :

alter table tag add unique index title(title);

Then, you can do the following

insert into tag (Title, TagCount) values ('blah',1)
on duplicate key update TagCount = TagCount + 1;

Hopefully that does the trick for you.

Answer 2

What you probably want to do is to make the 'Title' field in Tag Unique.

If you do that, then you could do:

INSERT INTO Tag (Title, TagCount) VALUES ('<value>', 1)

ON DUPLICATE KEY UPDATE TagCount = TagCount + 1;