Adding an auto increment collumn to a table afterwards.

[Rutger Heijmerikx]

Or, you can say too late ;)

What i'm trying to do is the following;

I have an existing table that doesn't have a auto increment field but i do want one now.
I've added a field already named 'mailrecordid' as INT and all the default values are now set to '0'.

I've tried the following but being a (My)SQL newbie i don't come far.

UPDATE listitem set mailrecordid = (select mailrecordid limit 1) + 1 where mailrecordid = 0 LIMIT 1

This of course updates only one record in a database of thousands records at this point.
I want to generate unique numbers for the existing records so i can begin using the auto increment field for the future records.

Thank you in advanced for your help.