一次更新/增加多行上的一列 [英] Update/Increment a single column on multiple rows at once

问题描述

我试图将行添加到列中，将最新列的order设置为1，所有其他行从那里开始递增计数.

I'm trying to add rows to a column, keeping the order of the newest column set to one, and all other rows counting up from there.

在这种情况下，我添加了一个新的order = 0的行，然后使用此查询将所有行更新一.

In this case, I add a new row with order=0, then use this query to update all the rows by one.

"UPDATE favorits SET order = order+1"

但是，发生的是所有行都更新为相同的值.我得到一堆收藏夹，例如全部为6阶，当它应该是1阶，然后是2阶，依此类推.

However, what happens is that all the rows are updated to the same value. I get a stack of favorites, all with order 6 for example, when it should be one with 1, the next with 2 and so on.

如何以一种应该对它们进行排序的方式来更新这些行?

How do I update these rows in a way that orders them the way they should be?

谢谢，
〜乔丹

Thanks,
~Jordan

推荐答案

您要告诉数据库的操作是通过将其顺序字段加1来更新表中的每条记录.因此，每条记录将始终具有相同的值.我相信您正在尝试将最新记录设置为1，将最旧记录设置为(无记录+1).

What you are telling the DB to do it update EVERY record in the table by incrementing its order field by one. So EVERY record will always have the same value. I beleive you are trying to set the latest record to 1 and the oldest record set to (no records+1).

所以也许您可以尝试以下方法:

So maybe you can try this:

set @count = (SELECT COUNT(ID) from favorits);
UPDATE favourits SET order = @count-ID+1

现在假设没有记录被删除.在这种情况下，您必须对此进行调整，并将最新记录设置为1，然后增加按ID排序的每个先前记录的顺序值.

Now this is assuming that no records are deleted. In that case you would have to adjust for this and set the latest record to 1 and then increment the order value for each previous record sorted by ID.

这就是方法:

set @i=0;
set @Count=(SELECT COUNT(*) from favorits);

UPDATE favorits SET `order` = @Count-(@i:=@i+1)+1;

这篇关于一次更新/增加多行上的一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！