How to synchronize database in INSERT INTO query
Here is my query.
INSERT INTO compare_moodle
(userid,
username,
firstname,
lastname,
courseid,
coursename)
SELECT mdl_user.id,
mdl_user.username,
mdl_user.firstname,
mdl_user.lastname,
mdl_course.id,
mdl_course.fullname
FROM mdl_user
JOIN mdl_role_assignments ra
ON mdl_user.id = ra.userid
JOIN mdl_role r
ON ra.roleid = r.id
AND r.id = 5
JOIN mdl_context c
ON ra.contextid = c.id
JOIN mdl_course
ON mdl_course.id = c.instanceid
When
mdl_usercompare_moodle
It's possible to use a VIEW in place of a table for the purpose you present. You'd do that like this:
CREATE OR REPLACE VIEW compare_moodle AS
SELECT mdl_user.id AS userid,
mdl_user.username,
mdl_user.firstname,
mdl_user.lastname,
mdl_course.id AS courseid,
mdl_course.fullname AS coursename
FROM mdl_user
JOIN mdl_role_assignments ra ON mdl_user.id = ra.userid
JOIN mdl_role r ON ra.roleid = r.id
AND r.id = 5
JOIN mdl_context c ON ra.contextid = c.id
JOIN mdl_course ON mdl_course.id = c.instanceid;
This gives you a virtual table that is always transactionally up to date with your source tables. SQL best practice teaches us that this approach, without data redundancy, is the best way to do what you want.
The problem, of course, is performance. If you use this compare_moodle view a lot you may have slowdowns. Correct indexing of your tables can practically eliminate those slowdowns. I suggest you get your application working with a view, and then figure out whether you have a performance problem.
What you asked for in your question is a so-called materialized view -- a view represented by an actual table. In some makes and models of DBMS you can give a command like CREATE OR REPLACE MATERIALIZED VIEW ... and the DBMS does all the work. But, MySQL doesn't offer that.
There are various MySQLish approaches to managing materialized views. See here for more information. http://dba.stackexchange.com/questions/86790/best-way-to-create-a-materialized-view-in-mysql But this approach is a big hassle, and a maintenance headache compared to using an ordinary view.