MySql UPDATE Multiple Values-CodePudding

I want to update the sort_id for all my users. For example, since there are 10 users, I want to specify sort_id from 0-9 respectively. I can do it using foreach in PHP, but it causes a lot of performance and time problems. Is there a method to do it without running each update query again?

UPDATE users SET sort_id=LAST_INSERT_ID(sort_id) 1 WHERE id IN(100,101,102,103,104)

what I really want to do users

#id - #sort_id
100, 0
101, 1
102, 2
103, 3
104, 4

CodePudding user response：

I don't know why you want to store redundant data which can be calculated by the value of another column from the same table. Data redundancy leads to data anomalies and corruption and should be always avoided in relational database systems.

If you need sort_id only on client side, just use a simple select.

SELECT id, RANK() OVER (ORDER BY ID) - 1 as sort_id
FROM users WHERE id BETWEEN 100 and 104

If you really want to store the sort_id, then use UPDATE with a subquery:

UPDATE users AS u JOIN 
 (SELECT id, RANK() OVER (ORDER BY id) - 1 AS sort_id
  FROM users WHERE id BETWEEN 100 AND 104) as s
ON u.id=s.id SET u.sort_id=s.sort_id

CodePudding user response：

You can use the row_number() function:

UPDATE users u
SET sort_id = t.rn
FROM (
    SELECT id, ROW_NUMBER() OVER(ORDER BY id) rn
    FROM users
) t
WHERE t.id = u.id

The subquery assigns a sequential number to the rows, which is then used to update the sort_id column.