Table structure:

pinfo_spell race | class | spell | active player guid | race | class player_spell guid | spell | active

It is necessary to compare the pinfo_spell and player tables for race and class , the matched results should be inserted into the player_spell table by guid (from the player table), spell and active (from the pinfo_spell table). As a result, we need to get data from two tables, combine and transfer them to the third.

  • insert into player_spell select p.guid, ps.spell, ps.active from player p, pinfo_spell ps where p.class = p.

2 answers 2

After joining the tables, duplicates are possible, so it is better to add DISTINCT:

 INSERT INTO player_spell (guid, spell, active) SELECT DISTINCT p.guid, s.spell, s.active FROM player p JOIN pinfo_spell s ON p.race=s.race AND p.class=s.class

and again, if you don’t need duplicates in payer_spell when re-inserting, you should insert only new values:

 INSERT INTO player_spell (guid, spell, active) SELECT DISTINCT p.guid, s.spell, s.active FROM player p JOIN pinfo_spell s ON p.race=s.race AND p.class=s.class WHERE NOT EXISTS( SELECT 1 FROM player_spell ps WHERE ps.guid=p.guid AND ps.spell=s.spell AND ps.class=s.class )

The task itself is a bit strange. You lose information about what race and class values ​​guid, spell, active came from.

    Will fit?

     INSERT INTO player_spell (guid, spell, active) SELECT p.guid, s.spell, s.active FROM player p JOIN pinfo_spell s ON p.race=s.race AND p.class=s.class