Update Spellpower Spells on Gear to Stats

[Barebones]

[SQL]Update Spell Stats to Stat Type Stats

Base SQL Query Reference:

update item_template set stat_type10 = 45 where spellid_1 = ## and stat_type10 = 0;
update item_template set stat_type10 = 45 where spellid_2 = ## and stat_type10 = 0;
update item_template set stat_type10 = 45 where spellid_3 = ## and stat_type10 = 0;
update item_template set stat_type10 = 45 where spellid_4 = ## and stat_type10 = 0;
update item_template set stat_type10 = 45 where spellid_5 = ## and stat_type10 = 0;
update item_template set stat_type9 = 45 where spellid_1 = ## and stat_type9 = 0;
update item_template set stat_type9 = 45 where spellid_2 = ## and stat_type9 = 0;
update item_template set stat_type9 = 45 where spellid_3 = ## and stat_type9 = 0;
update item_template set stat_type9 = 45 where spellid_4 = ## and stat_type9 = 0;
update item_template set stat_type9 = 45 where spellid_5 = ## and stat_type9 = 0;
update item_template set stat_type8 = 45 where spellid_1 = ## and stat_type8 = 0;
update item_template set stat_type8 = 45 where spellid_2 = ## and stat_type8 = 0;
update item_template set stat_type8 = 45 where spellid_3 = ## and stat_type8 = 0;
update item_template set stat_type8 = 45 where spellid_4 = ## and stat_type8 = 0;
update item_template set stat_type8 = 45 where spellid_5 = ## and stat_type8 = 0;
update item_template set stat_type7 = 45 where spellid_1 = ## and stat_type7 = 0;
update item_template set stat_type7 = 45 where spellid_2 = ## and stat_type7 = 0;
update item_template set stat_type7 = 45 where spellid_3 = ## and stat_type7 = 0;
update item_template set stat_type7 = 45 where spellid_4 = ## and stat_type7 = 0;
update item_template set stat_type7 = 45 where spellid_5 = ## and stat_type7 = 0;
update item_template set stat_type6 = 45 where spellid_1 = ## and stat_type6 = 0;
update item_template set stat_type6 = 45 where spellid_2 = ## and stat_type6 = 0;
update item_template set stat_type6 = 45 where spellid_3 = ## and stat_type6 = 0;
update item_template set stat_type6 = 45 where spellid_4 = ## and stat_type6 = 0;
update item_template set stat_type6 = 45 where spellid_5 = ## and stat_type6 = 0;
update item_template set stat_type5 = 45 where spellid_1 = ## and stat_type5 = 0;
update item_template set stat_type5 = 45 where spellid_2 = ## and stat_type5 = 0;
update item_template set stat_type5 = 45 where spellid_3 = ## and stat_type5 = 0;
update item_template set stat_type5 = 45 where spellid_4 = ## and stat_type5 = 0;
update item_template set stat_type5 = 45 where spellid_5 = ## and stat_type5 = 0;
update item_template set stat_type4 = 45 where spellid_1 = ## and stat_type4 = 0;
update item_template set stat_type4 = 45 where spellid_2 = ## and stat_type4 = 0;
update item_template set stat_type4 = 45 where spellid_3 = ## and stat_type4 = 0;
update item_template set stat_type4 = 45 where spellid_4 = ## and stat_type4 = 0;
update item_template set stat_type4 = 45 where spellid_5 = ## and stat_type4 = 0;
update item_template set stat_type3 = 45 where spellid_1 = ## and stat_type3 = 0;
update item_template set stat_type3 = 45 where spellid_2 = ## and stat_type3 = 0;
update item_template set stat_type3 = 45 where spellid_3 = ## and stat_type3 = 0;
update item_template set stat_type3 = 45 where spellid_4 = ## and stat_type3 = 0;
update item_template set stat_type3 = 45 where spellid_5 = ## and stat_type3 = 0;
update item_template set stat_type2 = 45 where spellid_1 = ## and stat_type2 = 0;
update item_template set stat_type2 = 45 where spellid_2 = ## and stat_type2 = 0;
update item_template set stat_type2 = 45 where spellid_3 = ## and stat_type2 = 0;
update item_template set stat_type2 = 45 where spellid_4 = ## and stat_type2 = 0;
update item_template set stat_type2 = 45 where spellid_5 = ## and stat_type2 = 0;
update item_template set stat_type1 = 45 where spellid_1 = ## and stat_type1 = 0;
update item_template set stat_type1 = 45 where spellid_2 = ## and stat_type1 = 0;
update item_template set stat_type1 = 45 where spellid_3 = ## and stat_type1 = 0;
update item_template set stat_type1 = 45 where spellid_4 = ## and stat_type1 = 0;
update item_template set stat_type1 = 45 where spellid_5 = ## and stat_type1 = 0;
update item_template set stat_value10 = @@ where spellid_1 = ## and stat_value10 = 0;
update item_template set stat_value10 = @@ where spellid_2 = ## and stat_value10 = 0;
update item_template set stat_value10 = @@ where spellid_3 = ## and stat_value10 = 0;
update item_template set stat_value10 = @@ where spellid_4 = ## and stat_value10 = 0;
update item_template set stat_value10 = @@ where spellid_5 = ## and stat_value10 = 0;
update item_template set stat_value9 = @@ where spellid_1 = ## and stat_value9 = 0;
update item_template set stat_value9 = @@ where spellid_2 = ## and stat_value9 = 0;
update item_template set stat_value9 = @@ where spellid_3 = ## and stat_value9 = 0;
update item_template set stat_value9 = @@ where spellid_4 = ## and stat_value9 = 0;
update item_template set stat_value9 = @@ where spellid_5 = ## and stat_value9 = 0;
update item_template set stat_value8 = @@ where spellid_1 = ## and stat_value8 = 0;
update item_template set stat_value8 = @@ where spellid_2 = ## and stat_value8 = 0;
update item_template set stat_value8 = @@ where spellid_3 = ## and stat_value8 = 0;
update item_template set stat_value8 = @@ where spellid_4 = ## and stat_value8 = 0;
update item_template set stat_value8 = @@ where spellid_5 = ## and stat_value8 = 0;
update item_template set stat_value7 = @@ where spellid_1 = ## and stat_value7 = 0;
update item_template set stat_value7 = @@ where spellid_2 = ## and stat_value7 = 0;
update item_template set stat_value7 = @@ where spellid_3 = ## and stat_value7 = 0;
update item_template set stat_value7 = @@ where spellid_4 = ## and stat_value7 = 0;
update item_template set stat_value7 = @@ where spellid_5 = ## and stat_value7 = 0;
update item_template set stat_value6 = @@ where spellid_1 = ## and stat_value6 = 0;
update item_template set stat_value6 = @@ where spellid_2 = ## and stat_value6 = 0;
update item_template set stat_value6 = @@ where spellid_3 = ## and stat_value6 = 0;
update item_template set stat_value6 = @@ where spellid_4 = ## and stat_value6 = 0;
update item_template set stat_value6 = @@ where spellid_5 = ## and stat_value6 = 0;
update item_template set stat_value5 = @@ where spellid_1 = ## and stat_value5 = 0;
update item_template set stat_value5 = @@ where spellid_2 = ## and stat_value5 = 0;
update item_template set stat_value5 = @@ where spellid_3 = ## and stat_value5 = 0;
update item_template set stat_value5 = @@ where spellid_4 = ## and stat_value5 = 0;
update item_template set stat_value5 = @@ where spellid_5 = ## and stat_value5 = 0;
update item_template set stat_value4 = @@ where spellid_1 = ## and stat_value4 = 0;
update item_template set stat_value4 = @@ where spellid_2 = ## and stat_value4 = 0;
update item_template set stat_value4 = @@ where spellid_3 = ## and stat_value4 = 0;
update item_template set stat_value4 = @@ where spellid_4 = ## and stat_value4 = 0;
update item_template set stat_value4 = @@ where spellid_5 = ## and stat_value4 = 0;
update item_template set stat_value3 = @@ where spellid_1 = ## and stat_value3 = 0;
update item_template set stat_value3 = @@ where spellid_2 = ## and stat_value3 = 0;
update item_template set stat_value3 = @@ where spellid_3 = ## and stat_value3 = 0;
update item_template set stat_value3 = @@ where spellid_4 = ## and stat_value3 = 0;
update item_template set stat_value3 = @@ where spellid_5 = ## and stat_value3 = 0;
update item_template set stat_value2 = @@ where spellid_1 = ## and stat_value2 = 0;
update item_template set stat_value2 = @@ where spellid_2 = ## and stat_value2 = 0;
update item_template set stat_value2 = @@ where spellid_3 = ## and stat_value2 = 0;
update item_template set stat_value2 = @@ where spellid_4 = ## and stat_value2 = 0;
update item_template set stat_value2 = @@ where spellid_5 = ## and stat_value2 = 0;
update item_template set stat_value1 = @@ where spellid_1 = ## and stat_value1 = 0;
update item_template set stat_value1 = @@ where spellid_2 = ## and stat_value1 = 0;
update item_template set stat_value1 = @@ where spellid_3 = ## and stat_value1 = 0;
update item_template set stat_value1 = @@ where spellid_4 = ## and stat_value1 = 0;
update item_template set stat_value1 = @@ where spellid_5 = ## and stat_value1 = 0;
update item_template set spellid_1 = 0 where spellid_1 = ##;
update item_template set spellid_2 = 0 where spellid_2 = ##;
update item_template set spellid_3 = 0 where spellid_3 = ##;
update item_template set spellid_4 = 0 where spellid_4 = ##;
update item_template set spellid_5 = 0 where spellid_5 = ##;

Stats to change:

Spell Power

Stat Type is 45
Spell ID List

Block

Stat Type is 15
Spell ID List

Parry

Stat Type is 14
Spell ID List

MP5

Stat Type is 43
Spell ID List

Hit

Stat Type is 31
Spell ID List

Crit

Stat Type is 32
Spell ID List

Defense

Stat Type is 12
Spell ID List

Dodge

Stat Type is 13
Spell ID List

Attack Power

Stat Type is 38
Spell ID List

Armor Penetration

Stat Type is 44
Spell ID List

Spell Penetration

Stat Type is 47
Spell ID List

Ranged Attack Power

Stat Type is 39
Spell Id List

If you want to help:

Instructions:
1. Replace the number 45 with the correct Stat Type ID.
2. Replace ## with the Spell ID.
3. Replace @@ with the amount of the stat that the spell gives.
4. Upload to Pastebin or ED Code Paste.
5. Link in this post.

Purpose:

It changes the stat type (10) of all items, if the item has the spell id that you specified in any of the categories 1-5. Then it changes that spell id on the item to 0 so it is no longer there. Lastly, it will change the value of the new stat (spell power) to whatever you set it to.

 

[Tommy]

Thanks. \o/

 

[Barebones]

Updated with correct SQL.

update item_template set stat_type10 = 45 where spellid_1 = ##;
update item_template set stat_type10 = 45 where spellid_2 = ##;
update item_template set stat_type10 = 45 where spellid_3 = ##;
update item_template set stat_type10 = 45 where spellid_4 = ##;
update item_template set stat_type10 = 45 where spellid_5 = ##;
update item_template set stat_value10 = ### where spellid_1 = ##;
update item_template set stat_value10 = ### where spellid_2 = ##;
update item_template set stat_value10 = ### where spellid_3 = ##;
update item_template set stat_value10 = ### where spellid_4 = ##;
update item_template set stat_value10 = ### where spellid_5 = ##;
update item_template set spellid_1 = 0 where spellid_1 = ##;
update item_template set spellid_2 = 0 where spellid_2 = ##;
update item_template set spellid_3 = 0 where spellid_3 = ##;
update item_template set spellid_4 = 0 where spellid_4 = ##;
update item_template set spellid_5 = 0 where spellid_5 = ##;

 

[Barebones]

Fyi, still working on this. There are a good bit of spells to change. Then I also need to create it for my server with the scaled down stats.

 

[Barebones]

Updated:
~SQL Code had to be longer to make sure it didn't replace other stats
~Uploaded Spell Lists for stats
~Added instructions

 

[Barebones]

Updated:
~All stats have been added
~All spell stat type values have been added
~All spell id lists have been added
~OP cleaned up