4 ms·
update todos set pos = pos+1 where pos >= 3 ORDER BY pos DESC Am I missing something?
by computerthings 2y ago
update todos set pos = pos+1 where pos >= 3 ORDER BY pos DESC
Am I missing something?
- seanhunter 2y agoYou are not[1]. This is not a complex problem but the author has managed to make a blog article out of it and come to the conclusion that rational numbers are required. The reason the author gives for rejecting your proposal is they have added a uniqueness constraint which makes the update awkward but you can indeed work around that in a variety of ways or just drop the uniqueness constraint altogether. [1] Although update doesn't have an "order by" clause[2] but I'm assuming you mean 'update' and then subsequently 'select' with the order by. I think if you actually want an update to proceed sequentially in some order you have to use a cursor. [2] Author is using postgres so this would be the relevant doc https://www.postgresql.org/docs/current/sql-update.html https://www.postgresql.org/docs/current/sql-update.html
- facturi 2y agoIt works in MariaDB.
- sam_lowry_ 2y agoYou are effectively updating many todos. The benefit of the solution presented by the author is not about performance, but rather minimizing change. Imagine a situation when multiple clients keep copies of todos and rely on optimistic locking for updates. This is a cool little problem brilliantly solved.
- beart 2y agoIn that case, your update filters on the user_id.
- sam_lowry_ 2y agoIn my real-life case, these were not todos, but cases reviewed by public agents. These rotate between different agents and supervisors. The ownership is just an attribute intended for humans. OTOH, rebalancing the order at night is not a problem.