List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:January 29 2003 11:02pm
Subject:Re: Increment in update
View as plain text  
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 :)

-- 
	Dan Nelson
	dnelson@stripped
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