Wednesday, June 10, 2009

Add auto_increment and primary key after table is created and already has rows

I needed to do this the other day and couldn't find any good examples on how to do this.

set @counter = 0;

update table
set id = (select @counter := @counter + 1 from dual);

This will allow you to replicate the behavior of auto_increment after the fact.