Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2005
    Posts
    15

    Unanswered: How to populate a new table with data from another?

    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
    Last edited by nickweavers; 02-19-08 at 05:57.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    not in a single query, no
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2005
    Posts
    15
    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;
    Last edited by nickweavers; 02-19-08 at 08:40.

  4. #4
    Join Date
    Jan 2005
    Posts
    15
    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

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •