MySQL alternative to on duplicate key update

Question

I'm trying to pull from multiple tables to insert into a table to create role assignments in moodle's database based on the categories that are created but I need it to update on duplicate key but I cant use ON DUPLICATE KEY UPDATE because the fields im trying to match on role id, context id, and user id are not primary keys in the mdl_role_assignments table.

insert into vclassmoodle.mdl_role_assignments (roleid,contextid,userid,timemodified,modifierid,itemid,sortorder) select 
mdl_role.id as roleid, mdl_context.id as contextid, mdl_user.id as userid, unix_timestamp() as timemodified, 3 as modifierid, 0 as itemid, 0 as sortorder
from
mdl_context
    left join
mdl_course_categories ON mdl_context.instanceid = mdl_course_categories.id
    left join
mdl_user ON mdl_course_categories.idnumber = mdl_user.idnumber
    join
mdl_role ON mdl_role.shortname = 'manager'
where mdl_context.contextlevel = 40 and mdl_course_categories.depth > 1

Let me know if I need to clarify on anything

Thanks

The fields don't need to be primary keys - just mark them unique. — , Apr 08 '14 at 03:31
They cant be marked as unique because they're not, a user could have multiple role assignments for different permissions on the same contextid or different contextids. The permission im giving them that needs to be inserted and updated is manager over their category(contextid). if I were to mess with the moodle table it would likely cause major problems within moodle. — MightyElectro, Apr 08 '14 at 12:42
Presumably there is something that is unique or you wouldn't know which row to update. I'd guess that a compound unique index might do what you need but if you don't want to change the schema then you're left with no option but to `SELECT` from the table first and `INSERT` or `UPDATE` based on the result. Watch out for race conditions - you might need to use transactions for these updates. — , Apr 08 '14 at 15:23
yeah that's my problem, what's unique about it is the contextid and roleid and userid altogether — MightyElectro, Apr 13 '14 at 18:29

score 0 · Answer 1 · answered Apr 22 '14 at 17:11

Just been having a look at the function role_assign() in /lib/accesslib.php

If there is a duplicate then it doesn't update, so you could just ignore duplicates.

Although you should really use the role_assign() function rather than insert data directly. In case the role assignment changes in the future, but also because it triggers a role_assigned event which might be used elsewhere.

Still use your query but ignore existing records and create a loop to call role_assign(), something like this

SELECT mdl_role.id as roleid,
       mdl_context.id as contextid,
       mdl_user.id as userid
FROM mdl_context
JOIN mdl_course_categories ON mdl_context.instanceid = mdl_course_categories.id
JOIN mdl_user ON mdl_course_categories.idnumber = mdl_user.idnumber
JOIN mdl_role ON mdl_role.shortname = 'manager'
WHERE mdl_context.contextlevel = 40
AND mdl_course_categories.depth > 1
AND NOT EXISTS (
    SELECT mdl_role_assignments.id
    FROM mdl_role_assignments
    WHERE mdl_role_assignments.roleid = mdl_role.id
    AND mdl_role_assignments.contextid = mdl_context.id
    AND mdl_role_assignments.userid = mdl_user.id
    AND mdl_role_assignments.itemid = 0
    AND mdl_role_assignments.component = '')

Note that a duplicate is a combination of roleid, userid and contextid but also component and itemid. So component = '' needs to be checked too.

MySQL alternative to on duplicate key update

1 Answers1