Making a Conditional MySQL Insert statement

Question

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

score 3 · Accepted Answer · edited Feb 13 '13 at 21:53

3

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...

edited Feb 13 '13 at 21:53

Bernhard Barker

54,589
14
104
138

answered Feb 13 '13 at 21:41

hd1

33,938
5
80
91

Don't think this is syntactically valid. It would need to be `INSERT INTO .... SELECT 248620, 8, 308 FROM .... WHERE NOT EXISTS.....` – Michael Berkowski Feb 13 '13 at 21:44
An `INSERT` statement has no `WHERE` clause on its own. – Michael Berkowski Feb 13 '13 at 21:45
Yes, I was thinking something else, apologies – hd1 Feb 13 '13 at 21:51
I'll have to take a look at it tomorrow when I come into work. Thanks! – Zeratas Feb 13 '13 at 22:03

Michael Berkowski · Answer 2 · 2013-02-14T01:48:09.867

The RDBMS is well-equipped to handle this, if you define the correct index.

Sounds like what you need is a compound UNIQUE index across all three columns. When you perform an INSERT IGNORE, the combination will be inserted if it does not already exist.

Note that this will fail if you already have non-unique rows in your table.

ALTER TABLE SAKAI_REALM_RL_FN ADD UNIQUE KEY `idx_unique_realm_role_function` (REALM_KEY, ROLE_KEY, FUNCTION_KEY)

Then the INSERT selects all the REALM_KEY values and static values for the other 2 columns. If the values already exist, they're ignored. Otherwise they are inserted along with the REALM_KEY.

INSERT IGNORE INTO SAKAI_REALM_RL_FN (REALM_KEY, ROLE_KEY, FUNCTION_KEY) 
  /* SELECT within INSERT gets all REALM_KEY plus the 2 static values */
  SELECT 
    REALM_KEY, 
    8, 
    308
  FROM SAKAI_REALM_RL_FN

Here's a demo

When you have completed the INSERT IGNORE, you can drop the UNIQUE KEY since it may no longer be needed.

ALTER TABLE SAKAI_REALM_RL_FN DROP KEY `idx_unique_realm_role_function`

score 0 · Answer 3 · answered Feb 13 '13 at 22:20

I wasn't quite sure what you wanted, but here's something that you might find useful.

Schema with few entries:

CREATE TABLE ALOHA (
  REALM_KEY VARCHAR(32) NOT NULL,
  ROLE_KEY VARCHAR(32) NOT NULL,
  FUNCTION_KEY VARCHAR(32) NOT NULL
  );

INSERT INTO ALOHA VALUES ('1', '1', '1');
INSERT INTO ALOHA VALUES ('1', '1', '2');
INSERT INTO ALOHA VALUES ('1', '2', '1');
INSERT INTO ALOHA VALUES ('1', '2', '2');
INSERT INTO ALOHA VALUES ('1', '2', '3');
INSERT INTO ALOHA VALUES ('1', '2', '4');

Try to insert 3 entries (only one gets inserted):

INSERT INTO ALOHA (REALM_KEY, ROLE_KEY, FUNCTION_KEY)
 SELECT * FROM (
  SELECT '1' AS REALM_KEY, '2' AS ROLE_KEY, '1' AS FUNCTION_KEY
  UNION ALL
  SELECT '1', '2', '3'
  UNION ALL
  SELECT '1', '2', '5'
 ) s 
 WHERE NOT EXISTS
  (SELECT 1 FROM ALOHA a
   WHERE a.ROLE_KEY = s.ROLE_KEY
   AND a.REALM_KEY = s.REALM_KEY
   AND a.FUNCTION_KEY = s.FUNCTION_KEY);

Making a Conditional MySQL Insert statement

3 Answers3

Here's a demo