Syntax error in Update query using variables

[John White]

Hi all,
I have been creating an UPDATE query that essentially guarantees that a field (weight) will have consecutive entries starting at 0 from every unique 'page' and 'pagetype' in the table.
Version 5.0.41
My query is:

SET @p0=-1, @t0=NULL, @w=0;
UPDATE `tbl` SET @p1=col1, @t1=col2, @w=IF((@p0<>@p1)||(@t0<>@t1), 0, @w+1), @p0=@p1, @t0=@t1, col3=@w, col4='l' ORDER BY `col1`,`col2`;

I have tested the logic thoroughly in a SELECT query, fine. But the UPDATE query always produces a syntax error in the region of '@p1=col1, @t1=col2, @w=IF((@p0<>@p1)||(@t0<>@t1), 0, @w+1), @p0=@p1, @t0=@' - well pinpointed!

But I can find no syntax error!
Is there something you can do in select but not in update?
Can you not assign the output of the if() function to a variable???

A nudge in the right direction would be appreciated.
Cheers,
John



Edited 3 time(s). Last edit at 03/22/2009 10:34AM by John White.