MySQL INSERT multiple rows if certain values don't exist

Question

I have the following query which works correctly:

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;

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.

This is the structure of my Events table:

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;

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:

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))
);

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

I have also tried this alternative based on other StackOverflow posts:

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);

But this fails with "Can't specify target table for update in FROM clause" even if I try to return results using temporary tables.

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.

Thanks, Arj

It's always useful to include table definitions so that we can see the keys you have in place. That way we can say if using INSERT ON DUPLICATE KEY is appropriate. — P.Salmon
– P.Salmon, Commented Jul 29, 2019 at 9:59
have you read about INSERT ON DUPLICATE KEY UPDATE this may help. Thanks! — Shanteshwar Inde
– Shanteshwar Inde, Commented Jul 29, 2019 at 10:00
Good point about the table definition. I've updated my post to include the fact that event_ID is the PK (nothing else about the table is relevant). — Arj
– Arj, Commented Jul 29, 2019 at 10:08
Yes, I did read about INSERT ON DUPLICATE KEY UPDATE which gave me the idea for the second version of my query. However, this doesn't work due to using a SELECT ... FROM from the same table I'm INSERTing into. — Arj
– Arj, Commented Jul 29, 2019 at 10:10

forpas · Accepted Answer · 2019-07-29 14:16:39Z

Don't use VALUES, just INSERT ... SELECT and not FROM events.
Then UNION ALL.

This code works for MySql 5.6:

INSERT INTO Events (user_ID, event_type, event_creation_datetime, unit_ID)
SELECT * 
FROM (
  SELECT 10 user_ID, 'user_other_unit_moved' event_type, 
    now() event_creation_datetime, 8383 unit_ID
  UNION ALL
  SELECT 10, 'user_other_unit_moved', now(), 8380
) t
WHERE NOT EXISTS (
  SELECT 1 FROM Events e 
  WHERE e.event_type = t.event_type AND e.unit_ID = t.unit_ID
);

See the demo.

This code works for MySql 5.7+:

INSERT INTO Events (user_ID, event_type, event_creation_datetime, unit_ID)
SELECT * FROM (
  SELECT 10, 'user_other_unit_moved', now(), 8383
  WHERE NOT EXISTS (SELECT 1 FROM Events WHERE event_type = 'user_other_unit_moved' AND unit_ID = 8383)
  UNION ALL
  SELECT 10, 'user_other_unit_moved', now(), 8380
  WHERE NOT EXISTS (SELECT 1 FROM Events WHERE event_type = 'user_other_unit_moved' AND unit_ID = 8380)
) t

See the demo

And this for MySql 8.0+:

INSERT INTO Events (user_ID, event_type, event_creation_datetime, unit_ID)
SELECT 10, 'user_other_unit_moved', now(), 8383
WHERE NOT EXISTS (SELECT 1 FROM Events WHERE event_type = 'user_other_unit_moved' AND unit_ID = 8383)
UNION ALL
SELECT 10, 'user_other_unit_moved', now(), 8380
WHERE NOT EXISTS (SELECT 1 FROM Events WHERE event_type = 'user_other_unit_moved' AND unit_ID = 8380);

See the demo.

Gordon Linoff · Accepted Answer · 2019-07-29 11:14:12Z

1

Although you can write this with just union all:

INSERT INTO Events (user_ID, event_type, event_creation_datetime, unit_ID)
    SELECT x.user_id, x.event_type, now(), x.unit_id
    FROM (SELECT 10 as user_id, 8383 as unit_id, 'user_other_unit_moved' as event_type
         ) x
    WHERE NOT EXISTS (SELECT 1 FROM Events e2 WHERE e2.event_type = x.event_type AND e2.unit_ID = x.unit_ID)
    UNION ALL
    SELECT x.user_id, x.event_type, now(), x.unit_id
    FROM (SELECT 10 as user_id, 8380 as unit_id, 'user_other_unit_moved' as event_type
         ) x
    WHERE NOT EXISTS (SELECT 1 FROM Events e2 WHERE e2.event_type = x.event_type AND e2.unit_ID = x.unit_ID);

I suspect there is a better way. If a unit_id can have only one row for each event type, then you should specify that using a unique constraint or index:

create unique constraint unq_events_unit_id_event_type on events(unit_id, event_type);

It is better to have the database ensure integrity. In particularly, your version is subject to race conditions. And to duplicates being inserted within the same statement.

Then you can use on duplicate key to prevent duplicate rows:

INSERT INTO Events (user_ID, event_type, event_creation_datetime, unit_ID)
    VALUES (10, 'user_other_unit_moved', now(), 8383),
           (10, 'user_other_unit_moved', now(), 8380)
    ON DUPLICATE KEY UPDATE unit_ID = VALUES(unit_ID);

The update actually does nothing (because unit_ID already has that value). But it does prevent an error and a duplicate row from being inserted.

edited Jul 29, 2019 at 11:14

answered Jul 29, 2019 at 10:44

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

6 Comments

Arj Over a year ago

Hi Gordon, I've tried your first solution before and it produces the same error, although given that a couple of you have suggested this now (and I've also used the same example from another SO post) I'm beginning to suspect that there may be something wrong with my configuration. By the way, I can't have a unique constraint as unit_IDs can have multiple rows with the same event_type. This is because it's a multi-player game, and units can be moved multiple times (for example).

Gordon Linoff Over a year ago

@Arj . . . Good morning (where I am). MySQL requires a FROM clause to use WHERE. You can just add in FROM dual, but I prefer to list the values in a derived table so they don't have to be repeated in the EXISTS clause.

Arj Over a year ago

Hi Gordon, thanks very much - your edited answer is not presenting any syntax errors when I test it with EXPLAIN - so far so good. I will do a detailed test with some more code and report back with my results.

Arj Over a year ago

Hi Gordon, thanks very much for your help with this. I've awarded the answer to forpas as both your answers were effectively the same - I wish I could award answers to both of you!

Gordon Linoff Over a year ago

@Arj . . . I think you missed the point of this answer. You should be using a unique index/constraint to maintain data integrity. (And I'll point out that I fixed the initial code so it works hours before forpas.)

|

Collectives™ on Stack Overflow

MySQL INSERT multiple rows if certain values don't exist

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related