You shouldn't be storing the questio number at all. You have denormalised your database and this is why you are experiencing this problem.
Instead, store a unique identifier with each one (an 'id' field, autonumbering) and then, when you pull back all the questions, order it by this ID field. Your questions won't have numbers assigned to them but you can do that in whatever app uses the questions. Of course, the truth is that you _can_ do what you want just using SQL. But trust me, you don't want to. It would be at least O(n^2), I think, and would be a pain to write. On Friday 12 April 2002 2:38 pm, bin cai wrote: > Hello, verybody, > I am afraid to send my question again. > > i created a table in mysql database system to store > exam questions which has the following column fields: > questionnumber(integer),questionbody(String), answers > (String) and etc. > My problem is if i delete one row(question record) how > can iupdate the questionnumber in the easiest way > which > means mysql can do for me. Is it possible? or i have > to write a method to update questionnumber. > if In second case i got another worry. for example, > i wrote two querys,one is to delete this question > record another query is to update the > questionnumber,that is , all records whose > questionnumbers are greater than the questionnumber of > the deleted question should deducted by 1. if at the > time After the first query is executed successfully > and the second query is being executed, the computer > is crashed. the second query will be rolled back. so > the question is deleted but the questionnumber is not > updated. this will result in a mess. > If anybody can give me some hint i will appreicate > very much > Have a good weekend > bin > > > ______________________________________________________________________ > Music, Movies, Sports, Games! http://entertainment.yahoo.ca > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> Trouble > unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
