incuso wrote:
> I have a table in which a field (ordine) is used for the orderer
> output of data.
>
> This is not an index, since data are subdivided in different
> categories and therefore the value is not unique for the whole table.
>
> Sometimes I need to change the display order and I was used to use a
> query like this one:
>
> UPDATE globalmoneta as mon1,globalmoneta as mon2 SET
> mon1.ordine="2",mon2.ordine="3" WHERE mon1.ordine="3" AND
> mon2.ordine="2" AND mon1.parentid="W-CE3" AND mon2.parentid="W-CE3";
>
> probably not very efficient, but acceptable since this operation is
> not often performed.
>
> Unfortunately, after a server upgrade this query is no more working. I
> suspect this is due to a different mysql version but I can not check
> it.
>
> Any idea on how to obtain the same task with a different query?
>
> Thanks,
> Massimo

Try bracketing the tables this:
UPDATE (globalmoneta as mon1,globalmoneta as mon2) SET
mon1.ordine="2",mon2.ordine="3" WHERE mon1.ordine="3" AND
mon2.ordine="2" AND mon1.parentid="W-CE3" AND mon2.parentid="W-CE3";