Update/Increment a single column on multiple rows at once

I'm trying to add rows to a co开发者_如何学编程lumn, keeping the order of the newest column set to one, and all other rows counting up from there.

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"

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

SET @a = 0;  
UPDATE favorits SET order = @a:=@a+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

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.

So this would be the approach:

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

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

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?

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.

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