CODEWITHSUNDEEP
mysql
Ihor M.
Ihor M.

Reputation: 3148

How to self reference a record in MySQL in the INSERT INTO SELECT statement

I have a tree structure that I store in the table topic_nodes. There is a table called holders that all topic node tree belongs to. Now I need to insert a new ROOT topic node the statement looks like this:

        INSERT INTO topic_nodes 
        (id, holder_id, name, level_number, parent_id) 
        SELECT RIGHT(UUID_SHORT(), 10), id, '<ROOT>', -1, <value_of_id_that_was_just_generated> 
        FROM holders;

How do I insert the proper value instead of <value_of_id_that_was_just_generated> placeholder?

Upvotes: 0

Views: 98

Answers (1)

Barmar
Barmar

Reputation: 782105

Use a subquery that returns the UID, then you can reference that column twice in the main SELECT.

INSERT INTO topic_nodes 
(id, holder_id, name, level_number, parent_id) 
SELECT uid, id, '<ROOT>', -1, uid
FROM (SELECT RIGHT(UUID_SHORT(), 10) AS uid, id
      FROM holders) AS x

Upvotes: 1

Related Questions