INSERT INTO (Select ...)

Donovan · Client Joined: Oct 07, 2003 Posts: 691 Location: Ohio

Is this possible to do a subquery with a Insert Into?

For example:

Code:

$insertgrades = $db->sql_query("INSERT INTO ".$prefix."_tl_course_grade (Course_ID, SOMS_KEY, UID, Irat_Avg, Grat_Avg, Appex_Avg)
SELECT c.Course_ID, g.SOMS_KEY, g.UID, AVG(g.IRAT_Grade) AS Irat_Avg, AVG(g.GRAT_Grade) AS Grat_Avg, AVG(g.Appex_Grade) AS Appex_Avg
FROM ".$prefix."_tl_session_grades g
JOIN ".$prefix."_tl_session s ON (g.Session_ID = s.Session_ID)
JOIN ".$prefix."_tl_courses c ON (s.Course_ID = c.Course_ID)
WHERE c.Course_ID = '$Course_ID'");

montego · Posted: Mon Mar 17, 2008 6:14 am

Donovan, how did you make out on this?

Donovan · Client Joined: Oct 07, 2003 Posts: 691 Location: Ohio

This seems to be working for me.

Code:

$insertgrades = $db->sql_query("INSERT INTO ".$prefix."_tl_course_grades (Course_ID, SOMS_KEY, UID, Academic_Year, Irat_Avg, Grat_Avg, Appex_Avg) SELECT DISTINCT s.Course_ID, g.SOMS_KEY, g.UID, g.Academic_Year, AVG(IRAT_Grade) AS Irat_Avg, AVG(GRAT_Grade) AS Grat_Avg, AVG(Appex_Grade) AS Appex_Avg
FROM ".$prefix."_tl_session_grades g
JOIN ".$prefix."_tl_session s ON (g.Session_ID = s.Session_ID)
JOIN ".$prefix."_tl_courses c ON (s.Course_ID = c.Course_ID)
WHERE c.Course_ID = '$Course_ID'
GROUP BY g.SOMS_KEY");
if (!$insertgrades) {
   echo("<p>Error performing query: " . mysql_error() . "</p>");
   exit();
   }

montego · Posted: Wed Mar 19, 2008 5:28 am

Most excellent sir.