Mysql query help: sorting before updating

[AdrianD]

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?


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

[Shendare]

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.

[AdrianD]

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
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

[Shendare]

Cool. Glad you got it where you wanted it.

Looks like that results in the following:


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

[AdrianD]

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.