You could do it this way instead: UPDATE Stores SET `Order` = `Order` + 1; UPDATE Stores SET `Order` = 0 ORDER BY `Order` DESC LIMIT 1;
I would recommend changing the name of the column to something else though. 2009/12/1 Super Steve <[email protected]>: > You're right Chris, Ken's query did result in an error #1093. > > Maybe I should just do it as two separate queries, one to get the MAX > value and another to do the update. > > On Dec 1, 11:36 am, Chris Hope <[email protected]> wrote: >> That won't work in MySQL. You'll get this error message: >> >> #1093 - You can't specify target table 'Stores' for update in FROM clause >> >> And by the way, if you are going to use "Order" as a column name >> you'll need to enclose it in backticks in the SQL query otherwise it >> will fail on that count i.e.: this is right (even though the SQL will >> fail): >> >> UPDATE Stores Set `Order` = (SELECT (MAX(`Order`) + 1) FROM Stores >> LIMIT 1) WHERE `Order` = 0 >> >> 2009/12/1 Ken Golovin <[email protected]>: >> >> >> >> >> >> > just use a nested select. not tested, but this should work: >> >> > UPDATE Stores Set Order = (SELECT (MAX(Order) + 1) FROM Stores LIMIT 1) >> > WHERE Order = 0; >> >> > ----- Original Message ----- >> > From: "Super Steve" <[email protected]> >> > To: "NZ PHP Users Group" <[email protected]> >> > Sent: Tuesday, December 01, 2009 11:29 AM >> > Subject: [phpug] Re: Rotating Items on a Daily Basis? >> >> > Hmmm... >> >> > UPDATE Stores Set Order = MAX(Order) + 1 WHERE Order = 0; >> >> > Results in Error #1111 - Invalid use of group function >> >> > Note sure why.... >> >> > On Dec 1, 10:13 am, Simon J Welsh <[email protected]> wrote: >> >> One way would be to add a column to the table, say Order, with values from >> >> 1 to max. Then, using a crontab or some such, UPDATE Stores SET Order = >> >> Order - 1; UPDATE Stores Set Order = MAX(Order) + 1 WHERE Order = 0; >> >> >> Grab the stores sorted by Order. >> >> >> On 1/12/2009, at 10:09 AM, Super Steve wrote: >> >> >> > I've got a client that wants to display on his website a list of >> >> > retail stores that stock his products. To facilitate this we have all >> >> > of the store details in a MySQL database. >> >> >> > But rather than display all of the stores in alphabetical or location >> >> > order, my client wants to rotate the stores so each one gets a turn at >> >> > being at the top. The idea is that at the beginning of each day the >> >> > store that was on the top yesterday will move to the bottom and all >> >> > the other stores move up one. For the rest of the day the stores are >> >> > displayed in this order, i.e they maintain their positions for the >> >> > whole day. Then next day the top one goes to the bottom and they all >> >> > move up one. >> >> >> > Does anyone have any idea on how I can achieve this? I'm not looking >> >> > for actual PHP code, just pseudo code to get me on the right track. >> >> >> > -- >> >> > NZ PHP Users Group:http://groups.google.com/group/nzphpug >> >> > To post, send email to [email protected] >> >> > To unsubscribe, send email to >> >> > [email protected] >> >> >> --- >> >> Simon Welsh >> >> Admin ofhttp://simon.geek.nz/ >> >> >> Who said Microsoft never created a bug-free program? The blue screen >> >> never, ever crashes! >> >> >>http://www.thinkgeek.com/brain/gimme.cgi?wid=81d520e5e >> >> > -- >> > NZ PHP Users Group:http://groups.google.com/group/nzphpug >> > To post, send email to [email protected] >> > To unsubscribe, send email to >> > [email protected] >> >> > -- >> > NZ PHP Users Group:http://groups.google.com/group/nzphpug >> > To post, send email to [email protected] >> > To unsubscribe, send email to >> > [email protected] >> >> -- >> Chris Hope >> The Electric Toolbox Ltd >> >> Email: [email protected] >> Web:www.electrictoolbox.com >> Phone: +64 9 522 9531 >> Mobile: +64 21 866 529 > > -- > NZ PHP Users Group: http://groups.google.com/group/nzphpug > To post, send email to [email protected] > To unsubscribe, send email to > [email protected] > -- Chris Hope The Electric Toolbox Ltd Email: [email protected] Web: www.electrictoolbox.com Phone: +64 9 522 9531 Mobile: +64 21 866 529 -- NZ PHP Users Group: http://groups.google.com/group/nzphpug To post, send email to [email protected] To unsubscribe, send email to [email protected]
