Dal1980 Posted September 27, 2015 Share Posted September 27, 2015 Hi All I wonder if anyone know of a way around this scenario. IDCatItem11Pencil21Pen32Ruler42Rubber52Stapler63Paper73Highlighter If I delete Cat 1 from the list so the data now looks like IDCatItem32Ruler42Rubber52Stapler63Paper73Highlighter How do I rebuild the Cat groups so they would now look like the following? IDCatItem31Ruler41Rubber51Stapler62Paper72Highlighter I've used the following statement in the past for rebuilding indexes where they would be individual but this can't be used when I refer to an index list as a group (i.e. multiples of the same index). SET @var= 0; UPDATE `mytable` SET `orders` = (SELECT @var := @var +1) WHERE `cat` = '1' ORDER BY `orders` ASC Many thanks Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted September 27, 2015 Share Posted September 27, 2015 when I refer to an index yes, and what if your category values have been referred/linked/bookmarked to by visitors to your site. they would expect to be able to revisit your site and have their links/bookmarks take them to the same category information if it still exists. unless you are using test data in your tables that you will completely delete and start over with real data, you wouldn't ever alter the referral values between tables after it exists. Quote Link to comment Share on other sites More sharing options...
Dal1980 Posted September 27, 2015 Author Share Posted September 27, 2015 yes, and what if your category values have been referred/linked/bookmarked to by visitors to your site. they would expect to be able to revisit your site and have their links/bookmarks take them to the same category information if it still exists. unless you are using test data in your tables that you will completely delete and start over with real data, you wouldn't ever alter the referral values between tables after it exists. Thanks for your input. Unfortantely, your concerns are not relevant in this situation. I used false headings as my actual data is a lot more complex. Just to be clear, this isn't anything to do with a product shop or anything that even touches the URL. My question still stands (please don't worry about the premise of my question but rather the question itself). Many thanks Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted September 27, 2015 Solution Share Posted September 27, 2015 In this instance, $deletedCat = 1 UPDATE mytable SET cat = cat - 1 WHERE cat > $deletedCat But, as Mac_Gyver said, in a production environment, don't. Quote Link to comment Share on other sites More sharing options...
Dal1980 Posted September 27, 2015 Author Share Posted September 27, 2015 In this instance, $deletedCat = 1 UPDATE mytable SET cat = cat - 1 WHERE cat > $deletedCat But, as Mac_Gyver said, in a production environment, don't. Thanks Barand, I'll give that a try and write back. The actual data I have in my tables is a lookup of data groups. This is not related to any stationary or shop website. I just used the example above so that it made the example clear rather than complicating things with a 4 tier group system. Quote Link to comment Share on other sites More sharing options...
Dal1980 Posted October 1, 2015 Author Share Posted October 1, 2015 Sorry for the delay. Just wanted to report that everything worked spot on! Thanks very much Barand! 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.