CODEWITHSUNDEEP
mysql
Aleksandr Makov
Aleksandr Makov

Reputation: 2938

MySQL: How to insert multiple rows into one table for each id of another query?

I have a key-value table and I need to insert multiple rows for each selected id from another table.

  1. I get the user id:

    SELECT @id := id FROM user WHERE email = '[email protected]';

  2. I get the ids of things by user id:

    SELECT @things := id FROM `things` WHERE `owner_id` = @id;

  3. Now I need to insert multiple values into key-value table for each @things. And problem here is that I cannot use a select subquery, because I need to insert inline values, like:

    INSERT INTO key_value (key, value)
VALUES (
    @things, 'CUSTOM VALUE 1'
),
VALUES (
    @things, 'CUSTOM VALUE 2'
);

BUT, it does not work. The last query is obviously wrong. Any help would be much appreciated.

EDIT:

Looks like I need multiple queries to do that:

SELECT @id := id FROM user WHERE email = '[email protected]';

INSERT INTO key_value (`key`, `value`)
(
    SELECT id, 'CUSTOM_VALUE_1'
    FROM `things`
    WHERE `owner_id` = @id
);

INSERT INTO key_value (`key`, `value`)
(
    SELECT id, 'CUSTOM_VALUE_2'
    FROM `things`
    WHERE `owner_id` = @id
);

Upvotes: 0

Views: 2665

Answers (1)

Vikram Jain
Vikram Jain

Reputation: 5588

INSERT INTO key_value (key, value)
SELECT user.id as key, things.id as value FROM user
left outer join things on  user.id=things.owner_id
 WHERE user.email = '[email protected]'

Upvotes: 1

Related Questions