CODEWITHSUNDEEP
mysql
guy_m
guy_m

Reputation: 3307

MySQL - INSERT multiple values conditionally

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.

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

Upvotes: 2

Views: 2300

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

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

Upvotes: 4

echo_Me
echo_Me

Reputation: 37233

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

Upvotes: 0

Related Questions