Search code examples
mysqlsakai

Making a Conditional MySQL Insert statement

So I have this table named SAKAI_REALM_RL_FN that has 3 fields

  • REALM_KEY
  • ROLE_KEY
  • FUNCTION_KEY

What this statement needs to do is that if a certain 2 combinations of ROLE_KEY & FUNCTION_KEY don't exist for each REALM_KEY, than do an insert.

I was already taking a look at this StackOverflow post

I also have the query I was using for the singular inserts:

INSERT INTO `sakai`.`SAKAI_REALM_RL_FN` (`REALM_KEY`, `ROLE_KEY`, `FUNCTION_KEY`) VALUES (248620, 8, 308);

Psuedo-Code:

if(ROLE_KEY equals 8 and FUNCTION_KEY=308 don't exist for REALM_KEYS)
    than insert ROLE_KEY=8 & FUNCTION_KEY=308
Solution 
  • INSERT INTO `sakai`.`SAKAI_REALM_RL_FN` (`REALM_KEY`, `ROLE_KEY`, `FUNCTION_KEY`)
SELECT *primaryKey*
FROM `sakai`.`SAKAI_REALM_RL_FN`
WHERE not exists (SELECT *primaryKey*
                  from `sakai`.`SAKAI_REALM_RL_FN`
                  where role_key = 8 and function_key = 308);

    Hope that helps...