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 [EMAIL PROTECTED] http://www.newsclub.de - Der Meta-Nachrichten-Dienst --------------------------------------------------------------------- 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
