Question 21808 in Mysql Question posted in
The official MySQL documentation can be found here.

MySql UPDATE Multiple Values

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
Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search