conFIGuredAPK
conFIGuredAPK

Reputation: 13

Update multiple rows from one table to another

I am trying to update multiple rows from one table to another table.

I have this that works but I need to update 8 values in one query instead of 8 separate queries.

I need this

slot_id  |  spell_id
--------------------
0    |  1518
1    |  1519
2    |  1522
3    |  3476
4    |  4881
6    |  3475
7    |  4880

to this

spell1 | spell2 | spell3 | spell4 | spell5 | spell6 | spell7 | spell8
---------------------------------------------------------------------
   0   |   0    |   0    |    0   |   0    |     0  |   0    |    0

What I have accomplished is 1 at a time.

UPDATE instance_saved_info
INNER JOIN character_memmed_spells ON instance_saved_info.id = character_memmed_spells.id
SET instance_saved_info.Spell1 = character_memmed_spells.spell_id
WHERE character_memmed_spells.slot_id = 0

What I need to add in to make it work is this.

SET instance_saved_info.Spell2 = character_memmed_spells.spell_id
WHERE character_memmed_spells.slot_id = 1
SET instance_saved_info.Spell3 = character_memmed_spells.spell_id
WHERE character_memmed_spells.slot_id = 2
SET instance_saved_info.Spell4 = character_memmed_spells.spell_id
WHERE character_memmed_spells.slot_id = 3
SET instance_saved_info.Spell5 = character_memmed_spells.spell_id
WHERE character_memmed_spells.slot_id = 4
SET instance_saved_info.Spell6 = character_memmed_spells.spell_id
WHERE character_memmed_spells.slot_id = 5
SET instance_saved_info.Spell7 = character_memmed_spells.spell_id
WHERE character_memmed_spells.slot_id = 6
SET instance_saved_info.Spell8 = character_memmed_spells.spell_id
WHERE character_memmed_spells.slot_id = 7

Thank you

Upvotes: 0

Views: 140

Answers (2)

sgeddes
sgeddes

Reputation: 62851

Assuming I'm understanding your question correctly, you can use a subquery with conditional aggregation to pivot the 8 fields you need to update:

UPDATE instance_saved_info i
INNER JOIN (
    SELECT id, 
           max(case when slot_id = 0 then spell_id end) slot0,
           max(case when slot_id = 1 then spell_id end) slot1,
           ....
           max(case when slot_id = 7 then spell_id end) slot7
    FROM character_memmed_spells 
    GROUP BY id) c ON i.id = c.id
SET i.Spell1 = c.slot0, i.Spell2 = c.slot1, ..., i.Spell8 = c.slot7

Upvotes: 0

Rahul
Rahul

Reputation: 77896

Well you can declare multiple SET operator like

UPDATE instance_saved_info
INNER JOIN character_memmed_spells 
ON instance_saved_info.id = character_memmed_spells.id
SET instance_saved_info.Spell1 = character_memmed_spells.spell_id,
instance_saved_info.Spell2 = character_memmed_spells.spell_id2,
.....
WHERE character_memmed_spells.slot_id = 0

Upvotes: 1

Related Questions