MYSQL: How do I perform an INSERT inside a SELECT?

Question

I am attempting to perform an insert operation inside a select. This is for chaining multiple inserts together that are dependent on each other. As such it looks something like this.

INSERT (Parent)
--SELECT (Return the Child's uuid created and assigned by TRIGGER)
---INSERT (Child)

Every record in the database has a uuid assigned upon insert through a trigger. I have a table for each type of entity I am capturing (e.g. person,address,phone,email).

Solution 1: I can insert a dependent entity such as the phone first, select it's uuid, then include that in the parent entity. In this case "person".

Solution 2: I can create the parent entity, then the dependent, then update the parent after acquiring the dependents uuid.

Solution 3: What I want to do is create the dependents at the same time I am creating the parent, and return their uuid to the parent for the parent record insert. This relies on order of operations to ensure everything is happening in appropriate order.

Because of the issues with mysql array support, I'm fudging a parent/child uuid_array table. As such there are additional operations involved, but the given example is a lot simpler.

Any idea on how to pull this off? Is there a better way to return the uuid?

INSERT INTO person (Name_First,Name_Middle,Name_Last,Phone_UUID_Array)
VALUES  (
    'John',
    'Diddly',
    'Doe',
    SELECT @last_uuid
        INSERT INTO phone (Phone_Number,Phone_Type)
        VALUES  (
            '1-555-555-5555',
            (SELECT UUID FROM VIEW_TYPE_CATEGORY WHERE Reference_Type='Personal cell' AND Category='phone')
        )
);

Examples of triggers I've created for every table

CREATE TRIGGER uuid_person BEFORE INSERT ON person FOR EACH ROW SET NEW.UUID = UUID();
CREATE TRIGGER last_uuid_person AFTER INSERT ON person FOR EACH ROW SET @last_uuid = NEW.UUID;

CREATE TRIGGER uuid_phone BEFORE INSERT ON phone FOR EACH ROW SET NEW.UUID = UUID();
CREATE TRIGGER last_uuid_phone AFTER INSERT ON phone FOR EACH ROW SET @last_uuid = NEW.UUID;

http://stackoverflow.com/questions/7501464/how-to-get-the-id-of-inserted-row-in-mysql — Matthew Lock, Jun 18 '14 at 00:27
Also http://stackoverflow.com/questions/24275287/mysql-how-do-i-perform-an-insert-inside-a-select — Matthew Lock, Jun 18 '14 at 00:28
I've already looked into that one. Seems dependent on the auto-increment being set, for which I am not using. — CrashBandit, Jun 18 '14 at 00:33
I normally find it's easiest just to use a programming language to script all those inserts and selects and keep track of everything. Say perl or something. — Matthew Lock, Jun 18 '14 at 00:44
That's where I think I'm heading with this. Since I likely won't have any control of the interface I prefer to do as much as I can in the database, and provide simple views to use. I also want to use the functionality to establish automated logging of all record changes in the system. — CrashBandit, Jun 18 '14 at 00:46
For your last comment, you're going to need to look up MySQL triggers. I'm no expert in them, so I won't pretend to be. But that's what you're looking for. It'll basically take any changes made and put the values before hand into a different table that can't be accessed by anything other than the trigger script. [Documentation](http://dev.mysql.com/doc/refman/5.6/en/trigger-syntax.html) — Jhecht, Jun 18 '14 at 00:57
I actually have that working now. There are terrible downsides however. What I've done is assigned every AFTER INSERT trigger its own variable to write the uuid to. So in theory I can retrieve the uuid of the last insert for each table. This forces me into a situation however where I have to create each child and parent individually, which means individual transactions. In addition I have to be very careful not to overwrite a @last_uuid_tblname before I had the chance to capture it. I also might run into issues with multiple database connections and lots of transactions going on. — CrashBandit, Jun 18 '14 at 01:08
Maybe I should be asking how I can perform one INSERT inside another and ensure the inner ones execute before the outer. — CrashBandit, Jun 18 '14 at 01:14

score 1 · Answer 1 · edited May 23 '17 at 10:33

No, you cannot execute an INSERT "inside" a SELECT, nor can you execute an INSERT inside another INSERT.

You can play around with triggers, but IMHO that's more trouble than it's worth.

I would recommend not doing this with a trigger, but instead generate the uuid and save it in a session variable. Then you can use it in as many subsequent statements as you want.

SELECT UUID() INTO @phone_uuid;

INSERT INTO phone (Uuid,Phone_Number,Phone_Type) VALUES (@phone_uuid, ...);

INSERT INTO person (Name_First,Name_Middle,Name_Last,Phone_UUID_Array)
VALUES ('John', 'Diddly', 'Doe', @phone_uuid);

These session variables are scoped to the session, so you don't have to worry about concurrent clients doing their own work overwriting yours, even if each session is using the same variable names.

Also, I fear from your description that Phone_UUID_Array is a comma-separated list of UUID's. You are probably not going to be happy with that design, for a number of other reasons.

You've got the reference in the wrong direction. Person shouldn't try to have a reference to all its dependent phone numbers. Go the other way -- each row in phone should have a reference to its parent person. That way you don't need to have a comma-separated list, and you can insert them in a more sensible order -- parent first, then one or more phone rows that each references the same parent. You don't need to update parent after inserting the phone.

No comma separated list. Parent/Child relationships will be captured under it's own table, unless I can move to a postgre or something that supports arrays. I haven't thought about generating the UUIDs upfront like that. I'm going to look into it. Thanks for the info. For the references, they are bidirectional depending on which object you are viewing in which direction. I'm going for maximum flexibility in describing entities and their relationships. — CrashBandit, Jun 18 '14 at 01:46

MYSQL: How do I perform an INSERT inside a SELECT?

1 Answers1