470,833 Members | 1,203 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,833 developers. It's quick & easy.

Comiting changes(?) With MySQL

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
Jul 17 '05 #1
1 1384
"Sims" <si*********@hotmail.com> wrote in message
news:<2f************@uni-berlin.de>...

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?
Not off the top of my head...
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)?


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
Jul 17 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by dave | last post: by
8 posts views Thread by William Drew | last post: by
4 posts views Thread by Gordon Burditt | last post: by
39 posts views Thread by windandwaves | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.