how to swap two fields bertween two rows

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

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";

On 4 Feb, 16:49, "Paul Lautman" <paul.laut...@btinternet.com> wrote:
> Try bracketing the tables this:
>

Same result...

The query doeas not give any error. Simply only mon1.ordine is
changed...