Comiting changes(?) With MySQL

[Sims]

Hi,

I have a function to update some data, I run a set of 6 queries, ( all
updates and all on the same table).
I seem to run into problems where the data does not seem to be updated
properly or the update appears to be wrong.

What the function is doing, (try to do anyway), is

given some ID numbers A, B and the same table
I need to swap the values for both items...

C is a arbitrary number used for swapping the values...

update tablex set ID_NUMBER = C where ID_NUMBER = A
update tablex set SUBID_NUMBER= C where SUBID_NUMBER = A

update tablex set ID_NUMBER = A where ID_NUMBER = B
update tablex set SUBID_NUMBER =A where SUBID_NUMBER = B

update ID_NUMBER = B where ID_NUMBER = C
update SUBID_NUMBER =B where SUBID_NUMBER = C

To me that is not a very efficient way of doing things, would there be a
better query for swapping ID_NUMER/SUBIDNUMBER like code above?

Would I need to 'COMIT' the changes to ensure that they do not get mangled
by another query using the same table, (straight after that function)?

Many thanks

Sims

[Nikolai Chuvakhin]

Re: Comiting changes(?) With MySQL

"Sims" <siminfrance@ho tmail.com> wrote in message
news:<2fvb3gF2r 2upU1@uni-berlin.de>...[color=blue]
>
> I have a function to update some data, I run a set of 6 queries, ( all
> updates and all on the same table).
> I seem to run into problems where the data does not seem to be updated
> properly or the update appears to be wrong.
>
> What the function is doing, (try to do anyway), is
> given some ID numbers A, B and the same table
> I need to swap the values for both items...
>
> To me that is not a very efficient way of doing things, would there be a
> better query for swapping ID_NUMER/SUBIDNUMBER like code above?[/color]

Not off the top of my head...
[color=blue]
> Would I need to 'COMIT' the changes to ensure that they do not get mangled
> by another query using the same table, (straight after that function)?[/color]

The purpose of transactions is not to prevent "mangling by another
query". It is to ensure that either all queries in a batch are
executed or none is. In your case it seems to be a good idea to
do something like this:

BEGIN;
UPDATE tablex SET ID_NUMBER = C where ID_NUMBER = A;
UPDATE tablex SET SUBID_NUMBER = C where SUBID_NUMBER = A;
UPDATE tablex SET ID_NUMBER = A where ID_NUMBER = B;
UPDATE tablex SET SUBID_NUMBER = A where SUBID_NUMBER = B;
UPDATE tablex SET ID_NUMBER = B where ID_NUMBER = C;
UPDATE tablex SET SUBID_NUMBER = B where SUBID_NUMBER = C;
COMMIT;

Cheers,
NC