SitePoint Sponsor |
|
User Tag List
Results 1 to 3 of 3
Thread: complicated mySQL statement
-
May 21, 2002, 05:15 #1
- Join Date
- Mar 2001
- Location
- the Netherlands
- Posts
- 519
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
complicated mySQL statement
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,'');
-
May 22, 2002, 11:20 #2
- Join Date
- Oct 2001
- Location
- Whistler BC originally from Guelph Ontario
- Posts
- 2,175
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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...Maelstrom Personal - Apparition Visions
Development - PhP || Mysql || Zend || Devshed
Unix - FreeBSD || FreeBsdForums || Man Pages
They made me a sitepoint Mentor - Feel free to PM me or Email me and I will see if I can help.
-
May 23, 2002, 04:28 #3
- Join Date
- May 2002
- Location
- Helsinki, Finland
- Posts
- 40
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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."Webmaster - The Tabworld.Com
Bookmarks