อัปเดตคอลัมน์ด้วยตัวเลขลำดับตามลำดับที่กำหนดใน MySQL

ใช้ตัวแปรผู้ใช้ (user-defined variable) เพื่อสร้างลำดับตัวเลขขณะอัปเดต

ไวยากรณ์

SET @yourVariableName = 0;
UPDATE yourTableName
SET yourColumnName = (@yourVariableName := @yourVariableName + 1)
ORDER BY yourColumnName ASC;

สร้างตารางตัวอย่าง

mysql> create table updateColumnDemo (
    -> Id int,
    -> OrderCountryName varchar(100),
    -> OrderAmount int
    -> );
Query OK, 0 rows affected (1.76 sec)

เพิ่มข้อมูลตัวอย่าง

mysql> insert into updateColumnDemo(Id,OrderCountryName) values(10,'US');
mysql> insert into updateColumnDemo(Id,OrderCountryName) values(20,'UK');
mysql> insert into updateColumnDemo(Id,OrderCountryName) values(30,'AUS');
mysql> insert into updateColumnDemo(Id,OrderCountryName) values(40,'France');

แสดงข้อมูลก่อนอัปเดต (OrderAmount เป็น NULL)

mysql> select * from updateColumnDemo;

ผลลัพธ์:

+------+------------------+-------------+
| Id   | OrderCountryName | OrderAmount |
+------+------------------+-------------+
| 10   | US               | NULL        |
| 20   | UK               | NULL        |
| 30   | AUS              | NULL        |
| 40   | France           | NULL        |
+------+------------------+-------------+

อัปเดตสร้างลำดับ 1,2,3,4

mysql> SET @sequenceNumber = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE updateColumnDemo
    -> SET OrderAmount = (@sequenceNumber := @sequenceNumber + 1)
    -> ORDER BY OrderAmount ASC;
Query OK, 4 rows affected (0.25 sec)

ตรวจสอบหลังอัปเดต

mysql> select * from updateColumnDemo;

ผลลัพธ์:

+------+------------------+-------------+
| Id   | OrderCountryName | OrderAmount |
+------+------------------+-------------+
| 10   | US               | 1           |
| 20   | UK               | 2           |
| 30   | AUS              | 3           |
| 40   | France           | 4           |
+------+------------------+-------------+

คำอธิบาย

• @sequenceNumber := @sequenceNumber + 1 เพิ่มค่าตัวแปรและคืนค่าใหม่ในครั้งเดียว
• ORDER BY กำหนดลำดับการอัปเดต (สำคัญมาก)
• MySQL 8.0+ สามารถใช้ ROW_NUMBER() OVER (ORDER BY ...) ใน SELECT ได้ แต่ UPDATE ยังต้องใช้วิธีนี้

กรณีใช้งานจริง

• สร้างลำดับการแสดงผล (display order)
• ปรับลำดับความสำคัญ (priority)
• กำหนดหมายเลขคิว (queue number)