How to increment a field in a table by user?

Question

MySQL

I'm trying to make bookmarks with the ability to change the order. There are users with a one-to-many relationship to bookmarks.
Added an integer priority field to bookmarks.
I roughly understand how to increment a field in a common heap in a migration: or
Bookmark.update_all('priority = priority + 1')

ActiveRecord::Base.transaction do
  ActiveRecord::Base.connection.execute('SET @pri := 0;')
  ActiveRecord::Base.connection.execute('UPDATE bookmarks SET priority = ( SELECT @pri := @pri + 1 ) ORDER BY updated_at ASC;')
end

Which will give the following:
bookmarks:

user_id: 1, priority:1
user_id: 2, priority:2
user_id: 1, priority:3
user_id: 2, priority:4

And how to make so that the field was incremented by the user? Those. to get:
bookmarks:

user_id: 1, priority:1
user_id: 2, priority:1
user_id: 1, priority:2
user_id: 2, priority:2

The base is large, so I'm looking for a solution with the maximum use of database tools (Mysql).

Answer 1

1) Retrieve all users.
2) For each user, we go over the bookmarks and assign a priority
To be honest, it is not clear why to look for a solution using the database. If the database is really huge (GB / TB of data), then we run step 2. into multiple threads.

Answer 2

https://apidock.com/rails/ActiveRecord/Base/increment!