Some SQL Query's for small population servers.

[Krugus]

I was reading in another thread how someone was wanting a SQL Query to run on their server to Adjust Mob hps and dmg output.

Well I'll share what I use on my server for those that want to adjust it and use it as they see fit.

Remember to BACKUP before making any adjustments (good habit to get into).

For Mob HP's I use the following Query: It uses a base hp formula then adjusts it from there based on the mobs Bodytype (Nastier mobs will have more hps IE Dragons, Giants, Avatars etc).

#HP Adjustment based on level, bodytype, fabled and bonus hps
update npc_types set hp = ((level * level) + (5+(level*5))) where id > 999;
update npc_types set hp = 4.5*hp where id > 999 and bodytype = 19;
update npc_types set hp = 4*hp where id > 999 and bodytype = 16;
update npc_types set hp = 3.52*hp  where id > 999 and bodytype = 30;
update npc_types set hp = 3.51*hp  where id > 999 and bodytype = 10;
update npc_types set hp = 3.5*hp  where id > 999 and bodytype = 9;
update npc_types set hp = 3.1*hp  where id > 999 and bodytype = 29;
update npc_types set hp = 1.5*hp  where id > 999 and bodytype = 6;
update npc_types set hp = 1.32*hp  where id > 999 and bodytype = 34;
update npc_types set hp = 1.31*hp  where id > 999 and bodytype = 26;
update npc_types set hp = 1.3*hp where id > 999 and bodytype = 12;
update npc_types set hp = 1.25*hp  where id > 999 and bodytype = 28;
update npc_types set hp = 1.24*hp  where id > 999 and bodytype = 27;
update npc_types set hp = 1.23*hp  where id > 999 and bodytype = 5;
update npc_types set hp = 1.75*hp  where id > 999 and bodytype = 4;
update npc_types set hp = 1.21*hp  where id > 999 and bodytype = 2;
update npc_types set hp = 1.16*hp  where id > 999 and bodytype = 11;
update npc_types set hp = 1.15*hp  where id > 999 and bodytype = 3;
update npc_types set hp = 1.11*hp where id > 999 and bodytype = 32;
update npc_types set hp = 1.1*hp  where id > 999 and bodytype = 7;
update npc_types set hp = (1.5*hp)+250 where Name LIKE "%Fabled%"; 
update npc_types set hp = hp +(level * 10) where id > 999;#extra hp bonus not multiplied by the above... if this is ran without where id>999 then you must run  update npc_types set hp = hp - (level * 10) where id < 999; to correct it

Ok on to damage: I looked at the avg dmg min & max dmg for each level and it was a very simple design. Max dmg is based on the mobs level x2 till around 40th then its x3 then it goes up from there. Granted some mobs hit alot harder than others for their level (Hill Giants come to mind). After looking over the data, I decided I wanted a smoother line of progression than what they had so I used this: Again it uses a base line for min/max dmg then I moded it further by npc bodytype and at the end if its a fabled mob.

update npc_types set mindmg= 1 ,maxdmg = 1 where id > 999 and level = 0;
update npc_types set mindmg= level*0.5 ,maxdmg = level*2 where id > 999 and level > 0 and level < 20; #level 1-19
update npc_types set mindmg= level*0.5 ,maxdmg = level*2.25 where id > 999 and level > 19 and level < 30;#level 20-29
update npc_types set mindmg= level*0.75 ,maxdmg = level*2.5 where id > 999 and level > 29 and level < 35;#level 30-34
update npc_types set mindmg= level*0.75 ,maxdmg = level*2.75 where id > 999 and level > 34 and level < 40;#level 35-39
update npc_types set mindmg= level*0.75 ,maxdmg = level*3 where id > 999 and level > 39 and level < 45;#level 40-44
update npc_types set mindmg= level*1 ,maxdmg = level*3.5 where id > 999 and level > 44 and level < 50;#level 45-49
update npc_types set mindmg= level*1 ,maxdmg = level*4 where id > 999 and level > 49 and level < 55;#level 50-54
update npc_types set mindmg= level*1.5 ,maxdmg = level*4.5 where id > 999 and level > 54 and level < 60;#level 55-59
update npc_types set mindmg= level*2 ,maxdmg = level*4.75 where id > 999 and level > 59 and level < 65;#level 60-64
update npc_types set mindmg= level*2 ,maxdmg = level*5 where id > 999 and level > 64 and level < 70;#level 65-69
update npc_types set mindmg= level*2.5 ,maxdmg = level*5.5 where id > 999 and level > 69 and level < 75;#level 70-74
update npc_types set mindmg= level*3 ,maxdmg = level*6 where id > 999 and level > 74 and level < 80;#level 75-79
update npc_types set mindmg= level*3.5 ,maxdmg = level*6.5 where id > 999 and level > 79;#level 80+
#adjust for body type from level 30 onward
update npc_types set maxdmg = maxdmg*1.025 where id > 999 and level > 29 and bodytype = 2;#lycanthrope
update npc_types set maxdmg = maxdmg*1.025 where id > 999 and level > 29 and bodytype = 7;#magical
update npc_types set maxdmg = maxdmg*1.05 where id > 999 and level > 29 and bodytype = 3;#undead
update npc_types set maxdmg = maxdmg*1.05 where id > 999 and level > 29 and bodytype = 11;#undead
update npc_types set maxdmg = maxdmg*1.05 where id > 999 and level > 29 and bodytype = 5;#construct
update npc_types set maxdmg = maxdmg*1.05 where id > 999 and level > 29 and bodytype = 6;#extraplanar
update npc_types set maxdmg = maxdmg*1.05 where id > 999 and level > 29 and bodytype = 12;#vampire
update npc_types set maxdmg = maxdmg*1.1 where id > 999 and level > 29 and bodytype = 4;#giant
update npc_types set maxdmg = maxdmg*1.1 where id > 999 and level > 29 and bodytype = 26;#dragon
update npc_types set maxdmg = maxdmg*1.1 where id > 999 and level > 29 and bodytype = 29;#dragon
update npc_types set maxdmg = maxdmg*1.1 where id > 999 and level > 29 and bodytype = 30;#dragon
update npc_types set maxdmg = maxdmg*1.1 where id > 999 and level > 29 and bodytype = 32;#dragon
update npc_types set maxdmg = maxdmg*1.15 where id > 999 and level > 29 and bodytype = 9;#bane giant
update npc_types set maxdmg = maxdmg*1.15 where id > 999 and level > 29 and bodytype = 10;#unknown
update npc_types set maxdmg = maxdmg*1.15 where id > 999 and level > 29 and bodytype = 16;#
update npc_types set maxdmg = maxdmg*1.15 where id > 999 and level > 29 and bodytype = 19;#
#adjust for Fabled Mob
update npc_types set mindmg = mindmg*1.1,maxdmg = maxdmg*1.2 where id > 99 and Name LIKE "%Fabled%";

Now the next Query I use on my Item database. I took advantage of minstatus table not being used (at least in my DB it wasn't). I wanted to change up the prices on all wearable items across the board. Granted its not a perfect solution but atm it will do for now. It still needs more tweaking.
I was tired of the simple store bought Plate/Chain mail items being grossly over priced so I came up with this to adjust not only the simple items but also the more powerful magical items as well. In simple terms it most of the attributes of an item and put a number number on it. Based on that number and other factors it will come up with a price for said item and when its done it will set minstatus back to 0. It should not effect not wearable items like spells, crafting supplies, gems etc but it did effect the food items with stats:

update items set minstatus = aagi + ac + accuracy + acha + adex + aint + asta + astr + attack + avoidance + awis + banedmgamt + banedmgraceamt + extradmgamt + damage*3 + damageshield + elemdmgamt + haste*3 + hp + regen + mana + manaregen + enduranceregen + spellshield + strikethrough;
update items set minstatus = minstatus + range where itemtype = 27;
update items set minstatus = minstatus + range where itemtype = 5;
update items set minstatus = minstatus + ac  where material = 1;
update items set minstatus = minstatus + ac * 2 where material = 2;
update items set minstatus = minstatus + ac * 2.5 where material = 10;
update items set minstatus = minstatus + ac * 3 where material = 3;
update items set minstatus = minstatus * (1 + (damage / delay)) where delay > 0;
update items set minstatus = minstatus + damage *2 where itemtype = 1;
update items set minstatus = minstatus + damage *2 where itemtype = 4;
update items set minstatus = minstatus + damage *2 where itemtype = 35;
update items set price = minstatus * (100 * (1 + magic * 1.125) ) where minstatus > 0 and itemtype != 27;
update items set price = (minstatus * (10*(1+ magic *1.123)))/3 where minstatus > 0 and itemtype = 27;
update items set price = ((price / 100)*20)/2 where itemtype = 7;
update items set price = ((price / 100)*20)/2 where itemtype = 19;
update items set price = 0 where summonedflag = 1;
update items set price = 0 where norent = 0;
update items set minstatus = 0;

Other misc Query's: Fixing Potions to cast fast and have a quick reuse timer and make all items useable by any race (class still restricted though):

#Potion Casting Time Fix
update items set casttime = 100, casttime_ = 100, recastdelay = 5 where itemtype =21;
#Make all items usable by any RACE
update items set races = 65535 where races > 0;

Anyways just wanted to share some of the SQL Query's I've used on my little server. Feel free to adjust them how you like, just remember to make regular backups before making any adjustments (good habit to get into!)