CODEWITHSUNDEEP
mysqlsql
Ted
Ted

Reputation: 4166

MySQL: copy data from table to another

I need to copy "user1_id" & "user2_id" from "battle" table to new table called "user_battle",

tables structure:

"battle" table (structure):

id     user1_id     user2_id
1          1          2
2          1          3
3          2          3
4          2          4
5          1          4

"user_battle" table (structure): (should be filled like this)

battle_id      user_id
   1               1
   1               2
   2               1
   2               3
   3               2
   3               3
   4               2
   4               4
   5               1
   5               4

thanks,

Upvotes: 0

Views: 49

Answers (4)

wvdz
wvdz

Reputation: 16651

Use a union.

INSERT INTO user_battle (battle_id, user_id)
SELECT id, user1_id
FROM battle
UNION ALL
SELECT id, user2_id
FROM battle;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271111

I think you can use union all to get the data you want:

select id, user1_id
from battle
union all
select id, user2_id
from battle;

You can put insert into user_battle(battle_id, user_id) before this statement for the insert.

Upvotes: 3

maček
maček

Reputation: 77826

insert into allows you to use a select to effectively "copy" data from one table to another.

By using union you can get separate rows for user1_id and user2_id.

insert into user_battle (battle_id, user_id)
(
  select id, user1_id from battle
  union
  select id, user2_id from battle
)

Upvotes: 1

Stefaan Neyts
Stefaan Neyts

Reputation: 2067

insert into user_battle select id, user1_id from battle And insert into user_battle select id, user2_id from battle

Upvotes: 0

Related Questions