mysql 更新int,mysql根据顺序使用int更新列

Lets say I have these columns

uniqueID|Money|Quantity|MoneyOrder|QuantityOrder

1|23|12||

2|11|9||

3|99|100||

What I want to do is update MoneyOrder and QuantityOrder based on the value of ORDER BY.

So the results would be:

uniqueID|Money|Quantity|MoneyOrder|QuantityOrder

1|23|12|2|1

2|11|90|1|2

3|99|100|3|3

I want the update to operate like an identity column without actually making it an identity column. I know that I could just order by 'x' and the order would be the result but I want to generate a report where you can see the item line by line.

Is something like this possible update mytable set Moneyorder = 'imnotsure' order by MoneyOrder asc ?

解决方案

SET @rownumber = 0;

update mytable set Moneyorder = (@rownumber:=@rownumber+1)

order by MoneyOrder asc

or to do it in a single query you can try

update mytable target

join

(

select id, (@rownumber := @rownumber + 1) as rownum

from mytable

cross join (select @rownumber := 0) r

order by MoneyOrder asc

) source on target.id = source.id

set Moneyorder = rownum