Help with Sql commands

[Julesx]

First off i'm completely new to running my own server but i'm attempting it to appease my wife so please bear with me.

Trying to do a global item scale of all equips/item by like 5x there normal stats
ex str/sta/agi/dex/int/wis/cha resists and so forth. What would be the commands to do this via Sql. Looked all over can't find any help with these other than mob scaleing.
UPDATE npc_types SET ac = level;
UPDATE npc_types SET mindmg = level;
UPDATE npc_types SET maxdmg = level + 1;
UPDATE npc_types SET str = level/2;
UPDATE npc_types SET sta = level/2;
UPDATE npc_types SET dex = level/2;
UPDATE npc_types SET agi = level/2;
UPDATE npc_types SET wis = level/2;
UPDATE npc_types SET _int = level/2;
UPDATE npc_types SET cha = level/2;
UPDATE npc_types SET mr = level/2;
UPDATE npc_types SET fr = level/2;
UPDATE npc_types SET cr = level/2;
UPDATE npc_types SET dr = level/2;
UPDATE npc_types SET pr = level/2;
UPDATE npc_types SET hp_regen_rate = (level/10);
UPDATE npc_types SET mana_regen_rate = (level/5);

But this makes server entirely too weak. Lol forgot to back up database and had to do fresh install again. So could def use explict dirrections or link to a guild with eqemu sql commands. http://www.w3schools.com/sql/sql_update.asp Doesnt help me much considering i don't know what fields to even type in or how to 5x the stats. Any help much appreciated.

[Tabasco]

With just SQL you aren't going to be making very meaningful formulas by level, and EQ doesn't really pay much attention to level after 45.

For your purposes you might do something like
UPDATE npc_types SET ac = ac / 2
UPDATE npc_types SET mindmg = mindmg / 2

Use the existing scaling and ignore levels so when you get to a special boss encounter they are still harder than a regular mob of their level elsewhere, just not as hard as they are on live.

For items you might do something like
UPDATE items SET dex = dex * 2 WHERE dex > 0

If you want to add additional related stats you might run something like
UPDATE items SET agi = dex WHERE dex > 0
UPDATE items SET haste = 30 WHERE agi > 15 AND haste < 30
UPDATE items SET mana = 300 WHERE int > 10 AND mana < 300
UPDATE items SET hp = 300 WHERE (sta > 10 OR str > 10) AND hp < 300

For better mysql documentation, go straight to the source.
http://dev.mysql.com/doc/

[c0ncrete]

to get a list of fields in the items table, use:

DESCRIBE items;

this link (http://projecteqemu.googlecode.com/svn/trunk/EQEmuServer/common/item_struct.h) will give you a little more info as to what some of the fields are for. scroll down to where it says "struct Item_Struct".

so for example, if you wanted to multiply the following fields by 5

sint8 CR; // Save vs Cold
sint8 DR; // Save vs Disease
sint8 PR; // Save vs Poison
sint8 MR; // Save vs Magic
sint8 FR; // Save vs Fire
sint8 AStr; // Strength
sint8 ASta; // Stamina
sint8 AAgi; // Agility
sint8 ADex; // Dexterity
sint8 ACha; // Charisma
sint8 AInt; // Intelligence
sint8 AWis; // Wisdom
sint32 HP; // HP
sint32 Mana; // Mana
sint32 AC; // AC

the command would be
UPDATE items SET
CR=CR*5,
DR=DR*5,
PR=PR*5,
MR=MR*5,
FR=FR*5,
AStr=AStr*5,
ASta=AStr*5,
AAgi=AAgi*5,
ADex=ADex*5,
ACha=ACha*5,
AInt=AInt*5,
AWis=AWis*5,
HP=HP*5,
Mana=Mana*5,
AC=AC*5;

you will need to be aware that any stat with a negative value will also be multiplied, so it won't make everything better/stronger/more appealing.

[Julesx]

So i'm reading this correctly. To 5x stats i'd simply type
UPDATE items SET CR=CR*5
And do this command for each stat i'd like to x ?

Don't think i'ma bother with mob scaleing hoping wife will just "think" they are scaled sense equips will balance out things for her and her bots lol.

[c0ncrete]

yes, * is the multiplication operator.
you might consider updating each stat individually, as tabasco suggested, to keep from multiplying negative values:

UPDATE items SET CR=CR*5 WHERE CR > 0;that would only multiply positive CR values.

additionally, you could run something like this:

UPDATE items SET CR=0 WHERE CR < 0;to set any negative CR values to 0.

[Julesx]

Yea those commands are working out nicely. Could use a couple more to finish up server. Sql command to jincrease all items drop chance and one to lower respawn on mobs higher than level say 51 to like an hour. Unless theres one to do like raid mobs only to hour respawn. Was thinking Update npcs set with hp >100k but thats whewre i'm lost. lol Tried georges Npc/loot editor but don't see how to do more than 1 item at a time or 1 zone at a time max.

[Tabasco]

Spawn times:
http://www.eqemulator.org/forums/showthread.php?t=35688&highlight=spawn+times

Under the new loot system increasing drop chance is pretty easy:
UPDATE lootdrop_entries SET chance = chance * 2

You might also look at loottable_entries and mess with droplimit and mindrop, but that could result in huge piles of unwanted loot.

[Julesx]

UPDATE lootdrop_entries SET chance = chance * 2 keeps saying error in syntax so not sure what is wrong...

[Julesx]

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Ignore that idiot don't have a clue what hes doin lol. forgot to add ; at end of command haha.

[Tabasco]

Concerning that lootdrop query, it's possible that it could set a chance higher than 100 and inside a loottable entry that has a droplimit.
I think the entry point for the lootdrop is randomized, so every possible drop is still there, but you might run
UPDATE lootdrop_entries SET chance = 100 WHERE chance > 100

[sorvani]

I think the entry point for the lootdrop is randomized, so every possible drop is still there

This is correct, the entry point to the loot drop is randomized each time.

[Julesx]

I'm stuck can anyone tell me why even when i run a command like
Update items set astr = astr *2 where astr > 0; i'm still getting tons of post luclin items with negitive stats. I've even tried running
Upadate items set astr = 0 where astr < 0; to try and correct the problems but doesnt seem to matter. Any help would would be greatly appreciated.

[Julesx]

Is there a sql command to just set item stats back to default something like
update items set astr = default lol

[Julesx]

ok nm just divided all the stats i multipled by same amount corrected eveything to base stats agian just can't * stuff more than 3x without getting stupid negetive stats for somereason.

[c0ncrete]

it's possible that the results of the math that you are doing are outside the range of the column you are working with or the range of the data type the server is expecting (or both).

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html
http://www.cplusplus.com/doc/tutorial/variables/