MySQL - INSERT multiple values conditionally

Question

Ok, so I have a table which holds bets on games.
The table holds the columns: user_id, event_id, bet.
A user can send his/her (multiple) bets to the server in one request.

I need to insert multiple bets using one query, while checking that none of the bets are on an event that already started/finished.
In case of at least 1 started/finished event, I don't really care if the whole query cancels, or just ignores the 'unqualified' bets.

Question
How can I insert multiple bets (rows) with one query, while conditioning the insert on a select statement (which checks for each of the events' statuses)?

Here is the query I would've used if it worked (and it doesn't of course):

INSERT INTO bet_on_event (user_id, event_id, bet)
VALUES (1,5,1), (1,6,2)
IF (SELECT COUNT(*) FROM events WHERE _id IN(5,6) AND status=0) = ?;

Explanation
1. As mentioned, the values are pre-made - requested by the user.
2. Games/events have status. 0 means a game hasn't started, so it's ok to bet.
3. The select statement just counts how many of the requested events have status 0.
4. The 'IF' should check if the count from (3) equals the number of events the user requested to bet on, thus confirming that all the events are ok to bet on.

The 'IF' should be replaced with something that work, and the whole statement can be replaced if you have a better idea for what I'm trying to achieve.

A simpler query (which isn't enough for my case, but works with 1 row) is:

INSERT INTO bet_on_event (user_id, event_id, bet)
SELECT 1,5,1 FROM dual
WHERE (SELECT COUNT(*) FROM events WHERE _id IN(5,6) AND status=0) = ?;

Any idea? Is this even possible? Betting is gonna be used a lot, so I want to do it as quick as possible - with 1 query.
Thank you so much.

EDIT
That is what I ended up doing, taken from Thorsten's answer (I changed it to a dynamically built query, as that is what I need):

var query='INSERT INTO bet_on_event (user_id, event_id, bet)';

    for(var i=0; i<eventIds.length; i++){
    query+= ' SELECT ' + userId + ',' + eventIds[i] + ',' + bets[i] 
       + ' FROM dual WHERE EXISTS (SELECT * FROM events WHERE id = ' + eventIds[i]
       + ' AND Status = 0)';
        if(i < eventIds.length - 1){  
             query += ' UNION ALL';
        }
    }

Where eventIds and bets are in a corresponding order (like a map)

EDIT 2
I also wanted to UPDATE the bets which already exist (in case the user wanted to...). So there's a need to update each row with the relevant bet in the array. This is the solution:

ON DUPLICATE KEY UPDATE bet=VALUES(bet)

Just added (concatenated) to the end of the query...

Thorsten Kettner · Accepted Answer · 2014-05-28 14:37:42Z

4

Does this work for you? It inserts 1,5,1 if there is no event for id 5 that has started. Same for 1,6,1 and id 6.

INSERT INTO bet_on_event (user_id, event_id, bet)
SELECT 1,5,1 FROM dual WHERE NOT EXISTS 
  (SELECT * FROM events WHERE _id = 5 AND Status <> 0)
UNION ALL
SELECT 1,6,1 FROM dual WHERE NOT EXISTS 
  (SELECT * FROM events WHERE _id = 6 AND Status <> 0);

EDIT: If you don't want to insert anything in case one or more of the games have started, you can simply replace WHERE _id = 5 and WHERE _id = 6 with WHERE _id IN (5,6). Or have just one exists clause:

INSERT INTO bet_on_event (user_id, event_id, bet)
SELECT *
FROM
(
  SELECT 1,5,1 FROM dual
  UNION ALL
  SELECT 1,6,1 FROM dual
) tmp
WHERE NOT EXISTS (SELECT * FROM events WHERE _id IN (5,6) AND Status <> 0);

edited May 28, 2014 at 14:37

answered May 28, 2014 at 11:10

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

guy_m Over a year ago

thx for the reply. Wouldn't this query be slow? A user can be on many events at once.. I'm checking if it's working right now..

Thorsten Kettner Over a year ago

This shouldn't be slow provided there is an index on _id. The real question is: Is this what you are looking for? This is based on what I gathered from your request, but it is absolutely possible that I misunderstand your data structure.

guy_m Over a year ago

It's perfect, and definitely does what I asked for (just finished checking it). Too bad MySQL can't show me a list of the ignored rows, which means that I need to select all bets with userId, compare, and then return the user/client which bet was placed and which wasn't..Well thx a lot, works like a charm:)

guy_m Over a year ago

And just in case I'll decide to use it instead, do you have any idea of how to cancel the WHOLE query if at least one of these selects results in EXISTS (meaning the game started/finished)

Thorsten Kettner Over a year ago

I've edited my answer to give you a solution for this.

echo_Me · Accepted Answer · 2014-05-28 12:03:02Z

0

have you tried with UNION ?

 INSERT INTO bet_on_event (user_id, event_id, bet)
 (SELECT 1,5,1 FROM dual
  WHERE (SELECT COUNT(*) FROM events WHERE _id IN(5,6) AND status=0) = ?
 UNION 
 SELECT 1,6,2 FROM dual
 WHERE (SELECT COUNT(*) FROM events WHERE _id IN(5,6) AND status=0) = ? );

edited May 28, 2014 at 12:03

answered May 28, 2014 at 11:07

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

2 Comments

Thorsten Kettner Over a year ago

Two queries in parentheses and a where clause outside both? I suppose this is syntactically incorrect. BTW: There is no need for UNION here, as 1,5,1 will never be the same as 1,6,1. No duplicates to remove hence, so better use UNION ALL.

Thorsten Kettner Over a year ago

Now that you changed the parentheses, the query after UNION has a where clause and the query before UNION does not.

Collectives™ on Stack Overflow

MySQL - INSERT multiple values conditionally

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related