Mysql Insert with subquery

Question

I'm trying to insert several records like this :

SET @user_id = (select `id` from users where `email` IN( '[email protected]', '[email protected]' ) );

SET @badge_id = (select `id` from badges where `slug` = 'elearning_nutrition');

INSERT INTO `instructor_badges` (`id`, `user_id`, `badge_id`, `is_active`, `is_manual`, `created`)
VALUES (UUID(), @user_id, @badge_id, '1', '0', NOW());

but im getting this error:

[ERROR in query 1] Subquery returns more than 1 row

In this case I would have to insert 2 records. How could I perform this query?

You want to insert two records? Or you just want to defeat the "Subquery returns more than 1 row" error? In the second case, just put "LIMIT 1" onto your subqueries. — dmgig
– dmgig, Commented Apr 24, 2015 at 19:04

Aaron Kent · Accepted Answer · 2015-04-24 19:38:03Z

1

You could try this. This will cartesian join users to badges, though. So if you select two users and two badges you will get four rows to insert. But I think that's what you are saying you want to achieve.

INSERT INTO instructor_badges (id, user_id, badge_id, is_active, is_manual, created)
SELECT UUID(), users.id, badges.id, '1', '0', NOW()
FROM users 
JOIN badge
WHERE users.email IN( '[email protected]', '[email protected]' )
&& badges.slug = 'elearning_nutrition'

answered Apr 24, 2015 at 19:38

Aaron Kent

6281 gold badge5 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

German Gabriel Over a year ago

Thank you in my case the badge is only one . What do you think if I do something like this which I see is working as well. SET @badge_id = (select id from badges where slug = 'elearning_nutrition'); INSERT INTO instructor_badges (user_id, id, badge_id, is_active, is_manual, created) select id , UUID(), @badge_id , '1', '0', NOW() from users where email IN( '[email protected]', '[email protected]' )

Aaron Kent Over a year ago

Yes, that looks lovely to me.

Aaron Kent Over a year ago

Note that you can also just use your subquery directly if you would like to avoid the @badge_id variable.

German Gabriel · Accepted Answer · 2015-04-24 19:50:47Z

0

OK I solved in this way , is there another better way because I had to write user_id in first position :

 SET @badge_id = (select `id` from badges where `slug` = 'elearning_nutrition');

INSERT INTO `instructor_badges` (`user_id`, `id`,  `badge_id`, `is_active`, `is_manual`, `created`)
select `id` , UUID(), @badge_id , '1', '0', NOW() from users where `email`   IN( '[email protected]', '[email protected]' )

answered Apr 24, 2015 at 19:50

German Gabriel

1423 silver badges11 bronze badges

2 Comments

Aaron Kent Over a year ago

Why did you have to write it in first position?

Aaron Kent Over a year ago

In the future, you can just edit my Answer rather than providing your own so that we get more consensus on the right answer. Thanks.

Collectives™ on Stack Overflow

Mysql Insert with subquery

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related