Need help adjusting prices of spells via SQL

[chrsschb]

I'm looking for a SQL script and since I'm pretty new at writing scripts I'm asking for help. I want to alter the price of all spells by this equation: Manacost * 40.

[louis1016]

the mana cost isnt stored in the spell scroll item, its stored in the spell table itself so im not sure if this is actually doable this way

[trevius]

Yeah, I think that should be doable. I haven't tested it, but the query should be something like this:

UPDATE items, spells_new SET items.price = (spells_new.mana * 40) WHERE items.scrolleffect = spells_new.id;

As always, it is recommended to do a DB or at least items table backup before running any queries you are not 100% sure about. If using Navicat, you can just right click the items table and make a copy of it from there (copy and paste), so you will have a backup in case something goes wrong with the price changes.

[chrsschb]

Originally Posted by trevius

Yeah, I think that should be doable. I haven't tested it, but the query should be something like this:

UPDATE items, spells_new SET items.price = (spells_new.mana * 40) WHERE items.scrolleffect = spells_new.id;

As always, it is recommended to do a DB or at least items table backup before running any queries you are not 100% sure about. If using Navicat, you can just right click the items table and make a copy of it from there (copy and paste), so you will have a backup in case something goes wrong with the price changes.

Thanks Trevius. I knew it was doable, just wasn't sure how. Will this pull the individual mana costs of each item and not just the first one?

[trevius]

Yes, it basically looks for a spell ID that matches the spell ID in the scrolleffect field of the item, and then uses the mana for that spell to set the price of the item. If there is no matching spell id for the scrolleffect field (such as items that are not scrolls), the price shouldn't be affected by that query. This particular query will update all scrolls at once, but you could specify more stuff in the WHERE clause to limit it in any way you want such as certain item or spell IDs or whatever.