EQEmulator Forums - Mysql query help: sorting before updating

EQEmulator Forums (https://www.eqemulator.org/forums/index.php)

- Support::Windows Servers (https://www.eqemulator.org/forums/forumdisplay.php?f=587)

- - Mysql query help: sorting before updating (https://www.eqemulator.org/forums/showthread.php?t=40005)

Mysql query help: sorting before updating

I've searched online for a solution and didn't find anything specific to my problem. Stackoverflow is a great source, btw.

The desired outcome will make updates to all level 1, then level 2, 3, 4 and up. This query does not do this. Is there a way to make the query sort by level first then apply updates?

Code:

update loottable_copy,npc_types set mincash = floor(0.2 * (pow(`level`,2)) - 3 * `level` + 5) 

        where npc_types.loottable_id = loottable_copy.id and npc_types.level < 71

        and loottable_copy.mincash != 0 and npc_types.name regexp'(^a\_|^an\_)' and race = 60;

Thanks

Hmm... what is the mincash expression you're trying to apply to the records?

The one above results in a negative number for levels 2 through 13, which is likely not intended.

EDIT: `mincash` is the minimum amount of copper coins which will drop from a given loottable
Perhaps it's considered sloppy writing to not encapsulate columns with accented quotes. Point taken.

Ah, thanks for the tip. The warning I was was receiving was based directly from the result being signed and datatype being unsigned. Need to get my hands on a graphing app to conceptualize better.

I'm pretty close to resolution. I reworded my searches and found something that seems to work at a general level.

Updated query

Code:

update (select `level`,loottable_id,race,name from npc_types order by `level` asc) npc

        join loottable_copy loot on loot.id = npc.loottable_id

        set `mincash` = abs(floor(0.2 * (pow(`level`,2)) - 3 * `level` +5)) 

        where npc.loottable_id = loot.id and loot.mincash != 0 

        and npc.level < 21 and npc.name regexp'(^a\_|^an\_)' and race = 60;

I think this can count as resolved. I need to tweak a few things and analyze results but, this part is exclusive to what I'm doing.

Thanks Shendare

Cool. Glad you got it where you wanted it.

Looks like that results in the following:

Quote:

level,mincash
1,2
2,1
3,3
4,4
5,5
6,6
7,7
8,7
9,6
10,5
11,4
12,3
13,1
14,2
15,5
16,8
17,11
18,15
19,20
20,25

Thank you sir.

Not quite what I wanted but pretty close. The lower levels are pretty inconsequential anyways.

I've tweaked the math in it to <(floor(0.17 * (pow(`level`,2)) - `level` +5))> and the results are a little better, and no need for abs with this change.