SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Evangelist jazztie's Avatar
    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,'');

  2. #2
    What? Maelstrom's Avatar
    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.

  3. #3
    SitePoint Enthusiast
    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

Posting Permissions

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