一次更新/增加多行上的一列

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.

我正在尝试向列中添加行，将最新列的顺序设置为1，所有其他行都从这里开始计数。

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

在本例中，我添加了一个order=0的新行，然后使用这个查询将所有行逐一更新。

"UPDATE favorits SET order = order+1"

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.

然而，所发生的是所有的行都被更新为相同的值。我得到了一堆收藏，都是6，例如，当它应该是1和1，下一个是2，以此类推。

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

如何更新这些行，使它们按照应该的顺序排列?

Thanks,
~Jordan

谢谢,约旦~

3 个解决方案

#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).

您要告诉DB做的是，通过将它的order字段增加一个，来更新表中的每一个记录。所以每条记录的值都是一样的。我相信你正在尝试把最新的记录设置为1，最古老的记录设置为(没有记录+1)。

So maybe you can try this:

也许你可以试试这个:

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

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.

现在假设没有记录被删除。在这种情况下，您将不得不对此进行调整，并将最新的记录设置为1，然后根据ID对之前的每一个记录增加订单值。

So this would be the approach:

这就是方法:

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

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

#2

SET @a = 0;  
UPDATE favorits SET order = @a:=@a+1;

#3

I got intereted, so I came up with the following solution. Consider the following table:

我很感兴趣，所以我想到了下面的解决办法。考虑下面的表格:

CREATE TABLE `placements` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `user` varchar(12) NOT NULL,
  `place` tinyint(3) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8; 

INSERT INTO `placements` (`id`, `user`, `place`) VALUES
(1, 'Adam', 1),
(2, 'Bill', 2),
(3, 'Carl', 3),
(4, 'Doug', 4),
(5, 'Eddy', 5),
(6, 'Frank', 6),
(7, 'George', 7),
(8, 'Harry', 8),
(9, 'Ian', 9),
(10, 'John', 10);

So, lets say you have John go up against Adam for the #1 place and John wins:

假设你让约翰和亚当竞争第一名，约翰赢了:

UPDATE placements 
SET place = place +1 
WHERE user != "John";

UPDATE placements 
SET place = 1 
WHERE user = "John";

John is now in first place, and everybody else was bumped down a position. Now lets say that George goes up against Carl for Carl's position (currently placement #4). George wins: That means George is now place #4, and Carl place #5. What happens to Georges old position of #8?

约翰现在是第一名，其他人都被压在了一个位置上。现在假设乔治和卡尔竞争卡尔的位置(目前排名第4)。乔治赢了:这意味着乔治现在排名第四，卡尔排名第五。乔治的8号位置发生了什么?

UPDATE placements 
SET place = place +1 
WHERE place > 3 
AND place < 9 
AND user != "George";

UPDATE placements 
SET place = 4 
WHERE user = "George";

So, it's really not that hard to do. You just have to know the current placement of some of your users, and adjust your MySQL queries as needed.

所以，这并不是很难做到的。您只需知道一些用户的当前位置，并根据需要调整MySQL查询。

If you paste these queries into your terminal or phpMyAdmin (or whatever you use), and follow along, you'll see that it works.

如果您将这些查询粘贴到您的终端或phpadmin(或任何您使用的内容)中，并继续执行，您将看到它是有效的。

#1