Update and Order By

[kickstart]

Hi

 

Using 5.0.51b

 

I have a table which contains results for various things. I need to keep a track of the updates to results (they are grouped by months anyway), so each result has a version number. On updating a result I want to increase the version number by one and then insert the updated result as version 0 (thus for most things I can just use version 0 and ignore trying to find the latest one by any other method).

 

I have the following SQL:-

 

UPDATE results AS a JOIN results AS b 
ON a.TestId = b.TestId AND a.ItemId = b.ItemId AND a.ResultMonth = b.ResultMonth AND a.Id = 85 
SET b.ResultVersion = b.ResultVersion + 1 
ORDER BY b.ResultVersion DESC

 

This takes the id of the result that is being updated, gets all the ids for the same result for the same month and increments them by 1

 

Without the ORDER BY it will work occasionally, but the trouble is that it will often update the version to land up with a duplicate on a key.

 

However the docs suggest that using multiple tables you cannot use the order by syntax:-

 

http://dev.mysql.com/doc/refman/5.0/en/update.html

 

Any suggestions?

 

All the best

 

Keith

[fenway]

A duplicate on a key? Where is the index?

[kickstart]

Hi

 

There is a unique index on ItemId, TestId , ResultMonth AND ResultVersion.

 

Without the order it can increase the ResultVersion in any order (ie, 1 can become 2 before 2 has become 3), and so generate a tempory duplicate, causing it to fail.

 

Rather than using a JOIN directly in the UPDATE I did try using an IN with a subselect, JOINing the table against itself in the subselect. However MySQL won't let me use the same table in the subselect as it is updating.

 

At the moment I have it working by doing one select to get a list of IDs, and then just putting that list into the brackets for the IN on the UPDATE. Does work but I would prefer to use a single statement if I could.

 

All the best

 

Keith

[fenway]

Rather than using a JOIN directly in the UPDATE I did try using an IN with a subselect, JOINing the table against itself in the subselect. However MySQL won't let me use the same table in the subselect as it is updating.

Even if you "fake" it by wrapping it in a SELECT * FROM ( .... ) ?

[kickstart]

Hi

 

Unfortunatly yes. Even tried using a view for the subselect.

 

All the best

 

Keith

[Mchl]

BEFORE UPDATE trigger?

[kickstart]

Hi

 

Err, might be missing something but not sure how that could help me. Or are you suggesting on an update I trigger an update of the matching record with the next version number?

 

All the best

 

Keith

[fenway]

Why not ON DUPLICATE KEY UPDATE?

[kickstart]

Hi

 

Mmmm, interesting idea. Will have a play. Looking at the manual pages though it suggests that it will only update one row while I will need to update possibly a dozen or so.

 

All the best

 

Keith

[fenway]

A dozen? Maybe i've lost you.

[kickstart]

Hi

 

The field is a version number, with version 0 always being the current version. Any updates increase all the version numbers for that record and insert the new one as 0 (idea is that for normal queries where I only want the current version I can just specify version 0 without needing to faff around finding the max version number).

 

Hence if there were version 0,1,2,3 and 4 and I insert a new one then there are 5 updates to do. Increasing 0 to 1 would get a duplicate key, but increasing 1 to 2 would also give a duplicate key.

 

While it sounds like this should be easily corrected using an order by clause on the update this isn't supported by MySQL when you are also using a table join in the update.

 

With ON DUPLICATE KEY UPDATE it just move the error on as the update done by that statement also gives a duplicate key error.

 

All the best

 

Keith

[fenway]

Maybe I'm missing something here... why not simply timestamp each update? Isn't the most recent the most up to date?

[kickstart]

Hi

 

I am trying to avoid that as that will make all the joins on the data far more complicated by having to get the rows with the max timestamp for any group of rows. Incrementing the version number means I can get away with only matching against version 0 which will make things far simpler and faster.

 

Only other solution would be to move older records onto an archive table while only the latest one stays on the normal table. But that means essentially duplicating tables.

 

All the best

 

Keith

[fenway]

I definitely hear what you're saying -- I've tried to tackle and workaround this issue many times.

 

My personal favourite involves an "activity" column -- only one record is ever "active" at any given time -- problem solved.

[kickstart]

Hi

 

That might be a way to do it. Will have more of a play, although it removes the unique index (although I can use a timestamp to make it unique).

 

All the best

 

Keith

[fenway]

Actually, since mysql allows NULL in uniquely indexed columns, you can still ensure only one active record at any time.

[kickstart]

Hi

 

Good point.

 

All the best

 

Keith