mysql update a column with an int based on order

identity, mysql, sql, sql-order-by

Solution

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

Problem

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

Original source

Related problems