Mysql updates column with int on request

Question

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

Thus, the results will be as follows:

 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 work as an identity column, without actually making it an identity column. I know that I could just order "x" and the result will be the result, but I want to generate a report in which you can see the element in turn.

Something like this is possible update mytable set Moneyorder = 'imnotsure' order by MoneyOrder asc ?

Answer 1

 SET @rownumber = 0; update mytable set Moneyorder = (@rownumber: =@rownumber +1) order by MoneyOrder asc 

or do it in one request that 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 

Answer 2

See the answers to this question:

Updating a column to contain a row position

 SET @counter = 0; UPDATE my_table SET MoneyOrder = @counter := @counter + 1 ORDER BY Money; SET @counter = 0; UPDATE my_table SET QuantityOrder = @counter := @counter + 1 ORDER BY Quantity;