mysql: insert with select

[johnd0e]

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

[TheDude]

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!)