Can i insert into data with inserted id in mysql? [duplicate]

I'm designing a comments MySQL db, and my comments table has fields:

• id primary key, auto-incr
• thread int, not null
• content

All replies to the same comment, in addition to that root comment must share the same thread. This is simple when a user is replying to a comment but what about when a new root comment is posted? I figured I would set thread=id for root comments.

Problem is, I don't know how to write a query that will reuse the just created id value within the same query when filling thread. Is this even possible?

I've tried

INSERT INTO `comments`
VALUES (NULL, LAST_INSERT_ID(), 'hi there')

This gives me the id from the previous insert, not the current one. Do I have to use 2 queries?

Solution 1:

Do I have to use 2 queries?

Yes. As you discovered, the id value hasn't been generated yet in a BEFORE INSERT trigger. But you can't change your NEW.thread value in an AFTER INSERT trigger.

You can't rely on reading the INFORMATION_SCHEMA, because you can cause a race condition.

You'll just have to do the INSERT, and then immediately execute:

UPDATE comments SET thread=id WHERE id=LAST_INSERT_ID() AND thread IS NULL;

If it's a root comment.

See also my past answers on the similar topic:

• Concatenating a string and primary key Id while inserting
• Two autoincrements columns or autoincrement and same value in other column