SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: MySQL statement

  1. #1
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation 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
    SitePoint Guru
    Join Date
    Feb 2002
    Posts
    625
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you mean you don't know how to use PHP for this or are you in an envirement that doesn't allow you too use PHP?
    Cause with PHP this would be a peace of cake, if for some reason you can`t use PHP i think you would be better of if you post this in the database forum...

    Best regards,
    datune

  3. #3
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In the environment I have the work with I can't use PHP... I know it's very easy with PHP.

    Maybe I should move it to databases

    Jazz

  4. #4
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Moved to a more appropriate forum

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  5. #5
    SitePoint Wizard Chris82's Avatar
    Join Date
    Mar 2002
    Location
    Osnabrück
    Posts
    1,003
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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, ...)
    the column "Updated" does not exist in the table tblStudenten.

    Maybe this helps:

    Code:
    /* this creates a copy of the table tblStudenten
       However a primary key is not copied AFAIK
     */
    
    CREATE TABLE tblLid AS 
    SELECT StudId, Roepnaam, Tussenvoegsel, Achternaam, 
    Geboortedatum, Email, Studnr FROM tblStudenten;
    
    /* add column "updated" */
    
    ALTER TABLE tblLid ADD updated INT(1) AFTER Studnr;
    
    /* maybe: */
    
    UPDATE tblLid SET updated = 0;
    
    /* rename column "StudId" to "LidID" */
    
    ALTER TABLE tblLid CHANGE StudId LidID;
    Last edited by Chris82; May 27, 2002 at 15:33.

  6. #6
    SitePoint Enthusiast
    Join Date
    Mar 2001
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Jazz,

    Try this:
    INSERT INTO tblLid (Roepnaam, Tussenvoegsel, Achternaam, Geboortedatum, Email, Studnr)
    SELECT S.Roepnaam, S.Tussenvoegsel, S.Achternaam, S.Geboortedatum, S.Email, S.Studnr FROM tblStudenten S;



    That should take all rows from tblStudenten and insert them into tblLid, leaving LidID and updated columns blank (null). Also make sure that those columns are nullable in the table definition. If they aren't simply add a '' in the select statement for those columns like "SELECT '' as tblLid, S.Roepnaam, S.Tussenvoegsel, S.Achternaam, S.Geboortedatum, S.Email, S.Studnr, '' as updated FROM tblStudenten S"

    Hope this helps.
    BlackCatt


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
  •