nizar a wrote:
> I do the following
> ALTER TABLE emails ADD num INT AUTO_INCREMENT FIRST, ADD UNIQUE KEY(num);
> It works great but if I delete one of the recordsthe 'num' column isn't updated,
Let's all be glad it isn't doing that ;-)
AUTO_INCREMENT's are normally used as primary keys, which should never change nor be reused when deleted.
> example.
> 1 f l
f_l@email.dom
> 2 ab cd
ad@bd.dom
> 3 ef gh
eh@fg.com
>
> When 2 ab cd
ad@bd.dom is deleted the DB looks like
> 1 f l
f_l@email.dom
> 3 ef gh
eh@fg.com
>
> I want it to be
> 1 f l
f_l@email.dom
> 2 ef gh
eh@fg.com
>
> Is it possible without deleting the 'num' column
> and adding it again everytime I delete a row?
If you do want a 1-2-3 counter for whatever reason, you can use a user variable:
SET @counter = 0;
SELECT
@counter := @counter + 1 AS c,
some_field
FROM foo
ORDER BY some_other_field;
--
felix