Reputation: 13
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
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
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