Indexing distinct values for a column

I have a table containing a column authors that contains names of authors for different articles (different rows). I want to add a new column that contains a unique index for every author,i.e. let’s say 12 for every occurrence of Dickens (just a random pick) in the author column.

Can it be done by a MySQL query or will I have to write a new script for doing this?

Answer

See SQL Fiddle

-- set up tables
create table Authors (
  id int(11) not null auto_increment,
  name varchar(64), 
  primary key(id)
  );

create table Books (
  id int(11) not null auto_increment,
  name varchar(64), 
  author varchar(64),
  primary key(id)
  );

-- populate Authors table, giving each author a unique ID
insert into  Authors (name)
select distinct author from Books;

-- Add an author_id column to the Books table
alter table Books add  author_id int(11) after author;

-- Update the Books table, filling in the author_ids
update Books b, Authors a
set b.author_id = a.id
where b.author = a.name;

-- If you don't care to retain the author name to author ID mapping,
-- you can then drop the Authors table.