mysql update a column with an int based on order
20,957
Solution 1
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
Solution 2
See answers to this question:
Updating column so that it contains the 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;
Author by
Admin
Updated on July 09, 2022Comments
-
Admin almost 2 years
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
andQuantityOrder
based on the value ofORDER 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
? -
mikewasmike over 7 yearsfor the second query, throws an error: Incorrect usage of UPDATE and ORDER BY
-
juergen d over 7 yearsI uodated the query. Can you give it a try?