MySQL更新递增(不自动递增)列值 [英] MySQL Update Incremented (Not Auto-Incremented) Column Values

问题描述

我有一个包含4列的表格，其中一列称为订单". 订单"不会自动增加，但是另一列"mapping_id"会自动增加.其他列包括"quiz_id"和"question_id".

I have a table with 4 columns, one of which is called "order". "Order" is not auto-incremented, but another column, "mapping_id" is auto-incremented. The other columns include "quiz_id" and "question_id".

我想将"order"的所有值更改为与question_id字段相同的升序.我的应用程序仅基于此订单"列进行排序，因此，我可以重新排序的唯一方法是编辑此列中的值.

I want to change all the values of "order" to go in the same ascending order as the question_id field. My application only sorts based on this "order" column, so the only way I can re-sort is to edit the values in this column.

我想知道的是如何编写更新查询以更改"order"的所有值以升序排列.我以为只是将"order"的值与"question_id"的值进行匹配，但是question_id从2开始而不是1，并且"order"必须从1开始.感谢您提供的任何帮助！

What I want to know is how to write an update query to change all the values of "order" to go in an ascending number sequence. I thought of just matching the value of "order" to that of "question_id", but question_id starts at 2 instead of 1, and "order" must begin at 1. Thanks for any help you can provide!

推荐答案

类似下面的内容可能会满足您的需求.它将根据question_id顺序为order分配增量值:

Something like the following might do what you are looking for. It will assign incrementing values to order based on the question_id ordering:

select @ordval := 0;
update thetable set `order` = 
        (select @ordval := @ordval + 1) order by question_id;

这篇关于MySQL更新递增(不自动递增)列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！