batch update causes problems

[Jimi]

Hi

I have a table of images. Each image belongs to a certain imageAlbum, and each image has an index number and a filename.

Now I want the users to be able to change the index number for the images in his/her own imageAlbum. And everything works fine, down to the update batch. lets say one album looks like this:

imageAlbumId, filename, imageIndex
_____3______,img1.jpg,_____1_____
_____3______,img2.jpg,_____2_____
_____3______,img3.jpg,_____3_____
_____3______,img4.jpg,_____4_____

And lets say the user wants it to look like this instead:
_____3______,img4.jpg,_____1_____
_____3______,img1.jpg,_____2_____
_____3______,img2.jpg,_____3_____
_____3______,img3.jpg,_____4_____

The update is performed in a prepared statement (java), and for each change it does "UPDATE images SET imageIndex=? WHERE imageIndex=? AND albumId=?".

But when the first imageIndex is changed (1 -> 2) then the second change (2 -> 3) will change not only img2.jpg but also img1.jpg, because the current (new) index is infact 2. Is there a way to make the WHERE clause to use the OLD values? i.e the values from how the table looked BEFORE the first update.

/Jimi