How to add extra row with insert into table select from table in mysql?

Question

I have two tables: table_1 contains a post and table_2 contains all users mapped to post. now I would like to insert post into table1 and I will get a post_id, then I will insert all users who are mapped to post with user_id and post_id.

 insert into `post_users_map` (`post_id`,`user_id`,`is_owner`)
 select '12345', `user_id`, '0' from `users`
 where username in ("A","B","C")

Now I would like to insert ("12345",'123',1) as another row along with select results.

Any suggestion is appreciated and Thanks in Advance.

How about UNION?

Strawberry
– Strawberry

2019-02-09 09:41:12 +00:00
Commented Feb 9, 2019 at 9:41 — Strawberry
– Strawberry, Commented Feb 9, 2019 at 9:41
Thannk you @Strawberry, UNION worked.

Saikumar Anireddy
– Saikumar Anireddy

2019-02-10 12:36:41 +00:00
Commented Feb 10, 2019 at 12:36 — Saikumar Anireddy
– Saikumar Anireddy, Commented Feb 10, 2019 at 12:36

ScaisEdge · Accepted Answer · 2019-02-09 10:01:58Z

2

just use union for add the select

insert into `post_users_map` (`post_id`,`user_id`,`is_owner`) 
select '12345', `user_id`, '0' 
from `users` where username in ("A","B","C")
UNION 
select '12345', '123, '1'

answered Feb 9, 2019 at 10:01

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

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

Comments

LukStorms · Accepted Answer · 2019-02-09 11:20:20Z

1

Simply add another insert with values after the insert from a select.

insert into `post_users_map` (`post_id`,`user_id`,`is_owner`) 
select 12345, user_id, 0
from `users` 
where username in ('A','B','C');

insert into `post_users_map` (`post_id`,`user_id`,`is_owner`) values 
(12345, 123, 1);

Also, values allows to include more tupples in the same insert.

insert into `post_users_map` (`post_id`,`user_id`,`is_owner`) values 
(12345, 124, 1),
(12345, 125, 1),
(12345, 126, 1);

Or you could use a UNION ALL to insert user_id's from 2 select's with a different is_owner.

insert into `post_users_map` (`post_id`,`user_id`,`is_owner`) 
select 12345, user_id, 0
from `users` 
where username in ('A','B','C')
UNION ALL
select 12345, user_id, 1
from `users` 
where username in ('D');

edited Feb 9, 2019 at 11:20

answered Feb 9, 2019 at 10:54

LukStorms

29.8k5 gold badges36 silver badges49 bronze badges

Collectives™ on Stack Overflow

How to add extra row with insert into table select from table in mysql?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related