Mark, You could use the following: update t set num=num+1 order by num desc;
Joshua -- [EMAIL PROTECTED] -----Original Message----- From: Mark Rages [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 9:17 PM To: O'K Web Design Cc: [EMAIL PROTECTED] Subject: Re: I thought single UPDATE statements were atomic On Thu, Jun 05, 2003 at 09:06:43PM -0400, O'K Web Design wrote: > Hi > > You are not defining num so you are adding 1 to 0 to get 1 and you > already have that record. Sounds like you need an autoincrement field. > Mike I don't need an autoincrement field. In my actual application, the num is a sequence index that determines the order of some rows. When I want to reorder the rows, I need to change a series of numbers in this way. In my example I'm trying to take the table from one valid state to another valid state... if this syntax is wrong, how can I do it? Regards, Mark [EMAIL PROTECTED] > > > ----- Original Message ----- > From: "Mark Rages" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: June 5, 2003 7:52 PM > Subject: I thought single UPDATE statements were atomic > > > > According to the docs, single update statements are atomic. > > > > So why doesn't this work? > > > > mysql> create table t (num INT, UNIQUE (num)); > > Query OK, 0 rows affected (0.00 sec) > > > > mysql> insert into t values ('1'); > > Query OK, 1 row affected (0.00 sec) > > > > mysql> insert into t values ('2'); > > Query OK, 1 row affected (0.00 sec) > > > > mysql> select * from t; > > +------+ > > | num | > > +------+ > > | 1 | > > | 2 | > > +------+ > > 2 rows in set (0.00 sec) > > > > mysql> update t set num=num+1; > > ERROR 1062: Duplicate entry '2' for key 1 > > mysql> > > > > Help! > > > > Regards, > > Mark > > [EMAIL PROTECTED] > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
