kickstart Posted January 8, 2010 Share Posted January 8, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/187716-update-and-order-by/ Share on other sites More sharing options...
fenway Posted January 8, 2010 Share Posted January 8, 2010 A duplicate on a key? Where is the index? Quote Link to comment https://forums.phpfreaks.com/topic/187716-update-and-order-by/#findComment-991394 Share on other sites More sharing options...
kickstart Posted January 9, 2010 Author Share Posted January 9, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/187716-update-and-order-by/#findComment-991405 Share on other sites More sharing options...
fenway Posted January 9, 2010 Share Posted January 9, 2010 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 ( .... ) ? Quote Link to comment https://forums.phpfreaks.com/topic/187716-update-and-order-by/#findComment-991507 Share on other sites More sharing options...
kickstart Posted January 10, 2010 Author Share Posted January 10, 2010 Hi Unfortunatly yes. Even tried using a view for the subselect. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/187716-update-and-order-by/#findComment-992291 Share on other sites More sharing options...
Mchl Posted January 10, 2010 Share Posted January 10, 2010 BEFORE UPDATE trigger? Quote Link to comment https://forums.phpfreaks.com/topic/187716-update-and-order-by/#findComment-992300 Share on other sites More sharing options...
kickstart Posted January 10, 2010 Author Share Posted January 10, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/187716-update-and-order-by/#findComment-992313 Share on other sites More sharing options...
fenway Posted January 16, 2010 Share Posted January 16, 2010 Why not ON DUPLICATE KEY UPDATE? Quote Link to comment https://forums.phpfreaks.com/topic/187716-update-and-order-by/#findComment-995946 Share on other sites More sharing options...
kickstart Posted January 16, 2010 Author Share Posted January 16, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/187716-update-and-order-by/#findComment-996035 Share on other sites More sharing options...
fenway Posted January 16, 2010 Share Posted January 16, 2010 A dozen? Maybe i've lost you. Quote Link to comment https://forums.phpfreaks.com/topic/187716-update-and-order-by/#findComment-996196 Share on other sites More sharing options...
kickstart Posted January 17, 2010 Author Share Posted January 17, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/187716-update-and-order-by/#findComment-996621 Share on other sites More sharing options...
fenway Posted January 20, 2010 Share Posted January 20, 2010 Maybe I'm missing something here... why not simply timestamp each update? Isn't the most recent the most up to date? Quote Link to comment https://forums.phpfreaks.com/topic/187716-update-and-order-by/#findComment-998445 Share on other sites More sharing options...
kickstart Posted January 20, 2010 Author Share Posted January 20, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/187716-update-and-order-by/#findComment-998565 Share on other sites More sharing options...
fenway Posted January 20, 2010 Share Posted January 20, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/187716-update-and-order-by/#findComment-998829 Share on other sites More sharing options...
kickstart Posted January 21, 2010 Author Share Posted January 21, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/187716-update-and-order-by/#findComment-999275 Share on other sites More sharing options...
fenway Posted January 21, 2010 Share Posted January 21, 2010 Actually, since mysql allows NULL in uniquely indexed columns, you can still ensure only one active record at any time. Quote Link to comment https://forums.phpfreaks.com/topic/187716-update-and-order-by/#findComment-999300 Share on other sites More sharing options...
kickstart Posted January 21, 2010 Author Share Posted January 21, 2010 Hi Good point. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/187716-update-and-order-by/#findComment-999304 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.