complicated mySQL statement

[jazztie]

I have a little problem:

I want to copy one table to another and unfortunately I cannot use PHP to do this. So, I have to use a pure mySQL statement to do so.

Now, the table I want to fill looks like this:
tblLid (LidID,Roepnaam,Tussenvoegsel,Achternaam,Geboortedatum,Email,Studnr,updated)

the table I want to use to fill tblLid:
tblStudenten (StudID, Roepnaam, Tussenvoegsel, Achternaam, Email, Studnr, Email, Betaald, ...)

As you can see is tblStudenten larger (more fields) than tblLid.

Now, how do I update or insert the stuff from tblStudenten to tblLid within one query without using PHP?

Any help is appreciated!

Jazz

PS:
I wrote this, but that doesn't work:
INSERT INTO tblLid (LidID,Roepnaam,Tussenvoegsel,Achternaam,Geboortedatum,Email,Studnr,updated) LEFT JOIN (SELECT S.Roepnaam, S.Tussenvoegsel, S.Achternaam, S.Geboortedatum, S.Email, S.Studnr FROM tblStudenten AS S) VALUES ('',S.Roepnaam,S.Tussenvoegsel,S.Achternaam,S.Geboortedatum,S.Email,S.Studnr,'');

[Maelstrom]

You can't.

Yet.

Mysql doesn't support that level of foreign key joins. To update two tables you need to use two queries. ...

You may want to check out mysql4. I have heard it has support for triggers, foreign keys etc...

[Endy]

Yes you can. Use the insert...select statement described in the mySQL manual here

"With INSERT ... SELECT statement you can quickly insert many rows into a table from one or many tables."