title: MySQL INSERT multiple rows if certain values don't exist
body: <p>I have the following query which works correctly:</p>
  
  <pre><code>INSERT INTO Events (user_ID, event_type, event_creation_datetime, unit_ID)
  SELECT 10, 'user_other_unit_moved', now(), 8383
  FROM Events
  WHERE NOT EXISTS (SELECT event_ID FROM Events WHERE event_type = 'user_other_unit_moved' AND unit_ID = 8383)
  LIMIT 1;
  </code></pre>
  
  <p>What the query does is check to see if a row exists in my Events table that matches the event type and unit ID I wish to INSERT. If it finds an existing record, it does not proceed with the INSERT. However, if it does not find a record then it proceeds with the INSERT.</p>
  
  <p>This is the structure of my Events table:</p>
  
  <pre><code>CREATE TABLE `Events` (
    `event_ID` int(11) NOT NULL,
    `user_ID` int(11) NOT NULL,
    `event_type` varchar(35) NOT NULL,
    `event_creation_datetime` datetime NOT NULL,
    `unit_ID` int(10) UNSIGNED NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  ALTER TABLE `Events`
    ADD PRIMARY KEY (`event_ID`),
    ADD KEY `unit_ID` (`unit_ID`);
  
  ALTER TABLE `Events`
    MODIFY `event_ID` int(11) NOT NULL AUTO_INCREMENT;
  COMMIT;
  </code></pre>
  
  <p>The problem I have is trying to get the above query to work correctly when trying to INSERT multiple rows. I know how to INSERT multiple rows using comma delimited VALUES, but no matter what I try I get syntax errors. Here is the query I have been playing with:</p>
  
  <pre><code>INSERT INTO Events (user_ID, event_type, event_creation_datetime, unit_ID)
  VALUES (
  (SELECT 10, 'user_other_unit_moved', now(), 8383
  FROM Events
  WHERE NOT EXISTS (SELECT event_ID FROM Events WHERE event_type = 'user_other_unit_moved' AND unit_ID = 8383)
   LIMIT 1)),
  (SELECT 10, 'user_other_unit_moved', now(), 8380
  FROM Events
  WHERE NOT EXISTS (SELECT event_ID FROM Events WHERE event_type = 'user_other_unit_moved' AND unit_ID = 8380)
   LIMIT 1))
  );
  </code></pre>
  
  <p>However, no matter what I try (inserting, removing parentheses etc.) I get either the generic "You have an error in your SQL syntax;" or "Operand should contain only 1 column".</p>
  
  <p>I have also tried this alternative based on other StackOverflow posts:</p>
  
  <pre><code>INSERT IGNORE INTO Events (event_ID, user_ID, event_type, event_creation_datetime, unit_ID)
  VALUES
  (SELECT (SELECT event_ID FROM Events WHERE event_type = 'user_other_unit_moved' AND unit_ID = 8383), 10, 'user_other_unit_moved', now(), 8383),
  (SELECT (SELECT event_ID FROM Events WHERE event_type = 'user_other_unit_moved' AND unit_ID = 8383), 10, 'user_other_unit_moved', now(), 8383);
  </code></pre>
  
  <p>But this fails with "Can't specify target table for update in FROM clause" even if I try to return results using temporary tables.</p>
  
  <p>Is it just an error with my syntax, or am I trying to do something not possible with the way my query is laid out? And if it's just an error, how would I write the query so that it works as I've intended? Note that I do not want to use multi-queries - I want the query to work as one statement.</p>
  
  <p>Thanks,
  Arj</p>