Jindan Zhou wrote:

> How do I create new column to store the them then? Since at the onset
> of the design, I just used one column for the full name, now I do
> want separate columns for first/middle/last name. Eventually I will
> delete the full name column.

ALTER TABLE
http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

ALTER TABLE mytable ADD COLUMN fname VARCHAR(40);
ALTER TABLE mytable ADD COLUMN mname VARCHAR(40);
ALTER TABLE mytable ADD COLUMN lname VARCHAR(40);

UPDATE mytable SET fname=SUBSTRING_INDEX(title, ' ', 1),
mname=SUBSTRING_INDEX(SUBSTRING_INDEX(title,' ', 2), ' ',-1),
lname=SUBSTRING_INDEX(title, ' ', -1);

ALTER TABLE mytable DROP COLUMN title


Of course before you do something like this, make a backup of the table, in
case something goes wrong, you can at least restore it.


--

//Aho