SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    May 2002
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question mysql: insert with select

    Hi everyone!
    I'm a newbie to mySQl and have been "awarded" with rebuilding the company membership database.
    Until now the database was only one big table. I developed a new DB design where I use several tables.

    Every record has it's new unique ID (int (4)), which is supposed to be the primary key. The problem arose when I tried to import the data from the old database. I have to insert into the main table (names) 2 more values (nat_id and c_id). Every time I try to do this, I get either an error msg or the values are inserted into new records and not into the corresponding ones.

    !!In a manual I read that it is possible to use an insert statement with a select statement on the same table if you use MyISAM as table format!!

    Here's the sql statement I used:

    insert into names (nat_id, c_id) select memberscopy.nation, memberscopy.course from memberscopy, names where names.firstname=memberscopy.name and names.lastname=memberscopy.surname;


    For any help I would be really grateful since I do not know what else to try.

    John

  2. #2
    SitePoint Enthusiast
    Join Date
    Apr 2002
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try REPLACE:

    REPLACE INTO B(id, Field1, Field2) SELECT id, Field1, Field2 FROM A WHERE id = id

    // A = old table, B = new table


    Perhaps your solution doesn't work because of what's mentioned here:


    http://www.mysql.com/documentation/m...ce.html#UPDATE

    6.4.3.1 INSERT ... SELECT Syntax

    The target table of the INSERT statement cannot appear in the FROM clause of the SELECT part of the query because it's forbidden in ANSI SQL to SELECT from the same table into which you are inserting.
    (The problem is that the SELECT possibly would find records that were inserted earlier during the same run. When using sub-select clauses, the situation could easily be very confusing!)


Bookmarks

Posting Permissions

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