nested insert in mysql for tagging

Question

i'd like to add a tag to a blogpost with a single sql statement.

say my tables would look as follows:

tags
+-------+-----------+
| tagid | tag       |
+-------+-----------+
|     1 | news      | 
|     2 | top-story | 
+-------+-----------+

tag2post
+----+--------+-------+
| id | postid | tagid |     
+----+--------+-------+
|  0 |    322 |     1 |
+----+--------+-------+

the problem i'd like to solve is inserting a new tag, retrieve it's id and then inset this new id into the relation table in a single sql statement.

INSERT INTO tag2post (postid, tagid)
VALUES
(
    332, # the post
    IF (
        (SELECT tagid FROM tags WHERE tag = 'new_tag'),
        (SELECT tagid FROM tags WHERE tag = 'new_tag'),
         # here is where i'd like to insert 
         # the new_tag and return it's id
        'i am lost here'
    )
)

Answer 1

You can't do this as a single insert because inserts are atomic--that is, the ID isn't determined until the statement completes.

Wrap both statements in a transaction and you will get your ID, and atomicity.