The order column is just a SMALLINT UNSIGNED column, that should contain the values 1-n for each iCategoryID. These are used to order the rows in each category.
I'm in the process of writing an UPDATE query that will reorder the columns in reverse order and redefine the order number, so there will not be any duplicate values or gaps in each iOrder group.
I think I've actually done what I intended to do, but I was wondering why the UPDATE seemed to work perfectly, while the equivalent (at least it looks equivalent to me) SELECT statement does not work at all.
=================================================================================
UPDATE pages SET
iOrder = IF(iCategoryID = @mGroup0, @iOrder := @iOrder + 1, @iOrder := 1),
iCategoryID = (@mGroup0 := iCategoryID)
ORDER BY
iCategoryID,
iOrder DESC;
=================================================================================
SELECT
iCategoryID,
iOrder,
IF(iCategoryID = @mGroup0, @iOrder := @iOrder + 1, @iOrder := 1) as iNewOrder,
(@mGroup0 := iCategoryID) as [EMAIL PROTECTED]
FROM pages
ORDER BY
iCategoryID,
iOrder DESC;
=================================================================================
From what I can tell in the online docs, both of these queries should, in fact, not work. Maybe I'm missing something here.
I can get the select to work if I do this:
SET @iOrder := 0, @mGroup0 := 0;
Before I run the query. But, I also have several other tables I need this query to work on, and sometimes the Grouping column is a string, and if @mGroup0 isn't the same type of value as the Grouping column before I enter the SELECT query, it fails. So I would need to determine the type and each group column, then set @mGroup0 to the appropriate type before I run the select.
All this isn't what I'm actually trying to accomplish, it was just a method I was using to develop the proper update statement.
It really surprised me when the UPDATE statement works as is, with no prior setting of @iOrder or @mGroup0.
How do the User variables in an UPDATE statement act differently than in a SELECT statement?
Thanks, Chris
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
