List:General Discussion« Previous MessageNext Message »
From:Christian Kohlschütter Date:January 30 2003 7:58am
Subject:Re: Increment in update
View as plain text  
Am Donnerstag, 30. Januar 2003 00:02 schrieb Dan Nelson:
> In the last episode (Jan 29), Igor Kutsyy said:
> > Could you help me with this. I`m trying to increment values of
> > primary auto_increment field from table, and ofcourse it is not
> > working. Could you tell me how to construct a query correctly.
> > +-------+--------------+------+-----+---------+----------------+
> >
> > | Field   | Type           | Null  | Key | Default | Extra          |
> >
> > +-------+--------------+------+-----+---------+----------------+
> >
> > | id      | int(11)         |         | PRI | NULL    | auto_increment |
> > | descr | varchar(255) | YES| MUL | NULL    |                     |
> >
> > +-------+--------------+------+-----+---------+----------------+
> >
> > mysql> update tb_roubr set id=id+1 where id>1 order by id;
>
> UPDATE statements return no records, so ORDER BY is meaningless.  You
> can't control the order the records are modified.  If your problem is
> that id is a primary key and it won't let you temporarily have
> duplicate id's, just drop the index, do your update, and recreate it.
> If you can't drop the index, you can sort of cheat by creating another
> unindexed column id1, then do three separate updates: SET id1=id, SET
> id=NULL, then finally SET id=id1.  You might be able to do the first
> two at the same time with "SET id1=id, id=NULL", but test it on a
> scratch table first :)

Actually, you _can_ use ORDER BY with UPDATE.

It works fine, if you do an

update tb_roubr set id=id+1 where id>1 order by id DESC

on your table.

DESC (=descending) means that UPDATE will iterate through the rows from the 
end (that is the highest number) to top (that is id=1). That way, you keep 
the primary key unique.

What you have done was an implicit ORDER BY id ASC (=ascending), which 
obviously failed.
-- 
Christian Kohlschütter
ck@stripped

http://www.newsclub.de - Der Meta-Nachrichten-Dienst

Thread
Increment in updateIgor Kutsyy29 Jan
  • Re: Increment in updateDan Nelson30 Jan
    • Re: Increment in updateChristian Kohlschütter30 Jan
      • Re: Increment in updateDan Nelson30 Jan
        • Re: Increment in updateChristian Kohlschuetter30 Jan
  • RE: Increment in updateSherzod Ruzmetov30 Jan