kickstart Posted January 16, 2010 Share Posted January 16, 2010 Hi I have a table which has the following fields (amoung others) Id = Int Unique id. TestId = Int ClauseId = Int Order = Int Basically in code a test has several clauses which are checked in order specified in the order column. This works fine. TestId, ClauseId and Order combined are unique. I want to provide a function to swap around the order (ie, allow a superuser to shuffle a clause up or down). However my mind is a blank as to an elegant way of doing this. Can do it in multiple stages (ie, renumber one to 0, then the other to the firsts original number then the first back to the 2nd ones number). I am sure I have missed something obvious though and there must be an elegant way of doing this. All the best Keith Quote Link to comment Share on other sites More sharing options...
fenway Posted January 16, 2010 Share Posted January 16, 2010 You can use FIELD() in your update statement... just specify the desired order. Quote Link to comment Share on other sites More sharing options...
kickstart Posted January 17, 2010 Author Share Posted January 17, 2010 Hi Err, think I missed explaining it correctly. Problem is that whichever order I do it in it will fail as there is a duplicate unique index. If 2 becomes 1 and 1 becomes 2 then at some point momentarily both will be 1 or both will be 2. I can see a few ways of doing it (delete both and reinsert, update one to a special value then update the 2nd to the 1st value and then update the 1st to the 2nd original value), but nothing elegant. All the best Keith Quote Link to comment Share on other sites More sharing options...
fenway Posted January 20, 2010 Share Posted January 20, 2010 Well, if you're in a transaction, you can set them all to NULL first. Quote Link to comment Share on other sites More sharing options...
kickstart Posted January 20, 2010 Author Share Posted January 20, 2010 Hi Excellent idea. That should help. Will have a play when I get a chance to get back to that app. Thanks All the best Keith Quote Link to comment Share on other sites More sharing options...
kickstart Posted January 21, 2010 Author Share Posted January 21, 2010 Hi Have used that idea. Still not that elegant (means using a SELECT and then 2 UPDATEs), but does work. All the best Keith Quote Link to comment Share on other sites More sharing options...
fenway Posted January 21, 2010 Share Posted January 21, 2010 Sad but true... it's really the UNIQUE key that's the issue. Quote Link to comment Share on other sites More sharing options...
kickstart Posted January 21, 2010 Author Share Posted January 21, 2010 Hi I probably could get rid of the unique key, but it just seems wrong taking that out when it should be unique (and having it non unique could result in some tests being performed in a random order). All the best Keith Quote Link to comment 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.