Allow negative values in the column (but don't use them). When modifying the values, give them their correct numeric value but make them negative. Then update the negative values to positive.
HTH, Tore. ----- Original Message ----- From: "Amittai Aviram" <[EMAIL PROTECTED]> To: "Mysql" <[EMAIL PROTECTED]> Sent: Tuesday, March 04, 2003 11:28 PM Subject: Reshuffling unique integers > I have a table in which the first column represents the order in which the > data in the rows will appear in an HTML table on a Web page. The column is > called "ordr" (to avoid conflict with the reserved keword "order"). This > column is a primary key, but the values are not auto-incremented, they are > assigned by hand at first and supposed to be revised by a PHP script. This > is where the problem comes in. Suppose I want to change the order of a > record with respect to the HTML table by changing the value of the "ordr" > column for that row. Suppose I have five rows: > > 1 > 2 > 3 > 4 > 5 > > And I want to move the row currently at ordr 4 to ordr 1. > > Step 1. First, I can set the row with ordr 4 to ordr 0 to set it aside: > > 1 > 2 > 3 > 0 > 5 > > Step 2. Then I would raise each item's ordr value by 1 if it is >= 1 and < > 4: > > 2 > 3 > 4 > 0 > 5 > > Step 3. Finally, I would change 0 to the desired target ordr value, 1: > > 2 > 3 > 4 > 1 > 5 > > But how can I accomplish step 2? It would seem at first as if the following > should work: > update table my_table set ordr = ordr + 1 where ordr >= 1 and ordr < 4; > > But this results in an error. When MySQL tries to update the first row from > 1 to 2, it causes a (temporary) duplication of the value 2 in this unique > (primary key) column. > > How else am I supposed to do this? > > Thanks! > > Amittai Aviram > [EMAIL PROTECTED] > > > > --------------------------------------------------------------------- > 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
