I am trying to copy data from one database table to another. Below is the MySQL syntax I am working with:

$array = [];
$q1 = DB::inst()->select( "stu_term" );
foreach($q1 as $r1) {
$array[] = $r1;

$bind = [ ":stuID" => $r1['stuID'],"termID" => $r1['termID'],":level" => $r1['level'] ];

$q3 = DB::inst()->query(  
        "INSERT INTO stu_term (stuID,termID,termCredits,level) 
        SELECT stuID,termID,SUM(attCred),level 
        FROM stu_acad_cred 
        WHERE stuID <> :stuID AND termID <> :termID AND level <> :level 
        GROUP BY stuID,termID,level)",

I am trying to avoid duplicate entries ergo my multiple 'AND' conditions and the 'GROUP BY' clause. The issue I am having is that only one row from the stu_acad_cred table is being inserted into the stu_term table. Any help with seeing why my current SQL code isn't working is greatly appreciated.

Edited by joshmac

3 Years
Discussion Span
Last Post by joshmac

I need to correct something I stated above. My issue is not with the initial insert, the issue is when new records are found in stu_acad_cred. A cron job is fired every hour. So when new records are found in stu_acad_cred grouped by stuID, termID and level, I want a record created in stu_term with some of the data from stu_acad_cred copied over which is not working.

Edited by joshmac


After some more testing, I found that INSERT IGNORE is what I need in this situation.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.