Hi,

I have a table that contains, amoung other things, peoples names (as a string "lastname,firstname"). I would like to normalise this by separating the names out into another table with seperate fields for firstname and lastname, and at the same time replace the names in the first table with an id that references the name in the new table.

Can this be done in a single query?

TIA,
Nick

not in a single query, no

Thanks.

I am trying to do it as two operations. The first is to copy data from the first table into the second:

INSERT INTO `persons` ( '', lastName, firstName )
SELECT DISTINCT SUBSTRING_INDEX( owner, ',' , -1 ) AS firstName, SUBSTRING_INDEX( owner, ',' , +1 ) AS lastName
FROM `machinestracker`;

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''',lastName, firstName)
SELECT DISTINCT SUBSTRING_INDEX(owner,',',-1) AS fir' at line 1

I can't understand what it is indicating as the syntax error. Can anyone help?

I used the following example query which is given on the MySQL website http://dev.mysql.com/doc/refman/5.0/...rt-select.html and added the extra stuff to split the owner field on the comma that delimits the owners last and first names.

INSERT INTO tbl_temp2 (fld_id)
SELECT tbl_temp1.fld_order_id
FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

Spotted it... I was getting confused between values and names in the field list for the INSERT and had "INSERT persons ('', lastName, firstName)" thinking I needed to assign '' to the auto-incrementing ID field of persons.

This now works:

INSERT persons (lastName, firstName)
SELECT DISTINCT SUBSTRING_INDEX(machinestracker.owner,',',-1) AS firstName, SUBSTRING_INDEX(machinestracker.owner,',',+1) AS lastName FROM machinestracker

Thx

nice one

not too many people know about or appreciate the beautiful SUBSTRING_INDEX function



also, next time, please post in the mysql forum

this question wasn't really about "Database Concepts & Design"

ta