MySQL case execute a query on the basis of result returned from a query

Question

What I want to achieve is, If the number of rows returned by the query:

SELECT count(*) FROM `games` WHERE cateID=2 AND active=1

is EQUAL to 0, I want to run an inserting query:

INSERT INTO games(cateID ,time,users,active)
                VALUES ('2', '1', '0', '1')

I tried using case like this:

SELECT CASE WHEN SELECT count(*) FROM `games` WHERE cateID=2 AND active=1)=0
    THEN INSERT INTO games(cateID ,time,users,active)
                VALUES ('2', '1', '0', '1')
END

But it returns error as:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT count(*) FROM `games` WHERE cateID=2 AND active=1)=0
    THEN INSERT INTO' at line 1

Note: Both queries when executed separately, execute without any error. How do I achieve this ?

EDIT:

Also tried this with IF,

SELECT if(count(*)==0,INSERT INTO games(cateID ,time,users,active)
                VALUES ('2', '1', '0', '1')) FROM `games` WHERE cateID=2 AND active=1

Still gives the same error. EDIT 2: By the suggested comment,

INSERT INTO games(cateID ,time,users,active)
select '2','2','0','1'
where (SELECT count(*) FROM `games` WHERE cateID=2 AND active=1) <= 0

Still gives error.

possible duplicate of [MySQL INSERT IF (custom if statements)](http://stackoverflow.com/questions/6854996/mysql-insert-if-custom-if-statements) — Mathew Tinsley, Mar 25 '15 at 21:03
http://stackoverflow.com/questions/18421905/mysql-insert-if-condition — d4Rk, Mar 25 '15 at 21:33

score 0 · Answer 1 · answered Mar 25 '15 at 21:30

0

Try this

DECLARE
  games_count INTEGER;
BEGIN
  SELECT count(*) FROM `games` WHERE cateID=2 AND active=1 ;
  IF games_count = 0 THEN
    INSERT INTO games(cateID ,time,users,active) VALUES ('2', '1', '0', '1');
  END IF;
END;

answered Mar 25 '15 at 21:30

mysqlrockstar

2,536
1
19
36

1

You need to select INTO games_count SELECT count(*) FROM games WHERE cateID=2 AND active=1 INTO games_count... P.S. You beat me for few second i just prepare to press Post Your Answer :D – Aleksandar Miladinovic Mar 25 '15 at 21:34
Gives an error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE games_count INTEGER' at line 1 – kritya Mar 28 '15 at 18:47

Eric Siodmak · Answer 2 · 2015-03-30T09:29:23.457

0

before to do the insert, check the select query. if the select query return 0 records nothing is inserting with the insert query.

select  cateID,'2' as time,'0' as users,active 
FROM 
   (select count(*) as countRecord,cateID,active 
    from games 
    where  CATEID=2 and active=1 group by cateID,active) G   
where countRecord <= 0  
group by cateID, active,time,users

edited Mar 30 '15 at 09:29

answered Mar 25 '15 at 21:56

Eric Siodmak

151
5

Still error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'active=1 group by cateID,active) where countRecord <= 0 group by cateID, activ' at line 5 – kritya Mar 28 '15 at 18:48
@kritya Oups sorry I just forget the `and` in where clause `CATEID=2 active=1` i' I have corrected this – Eric Siodmak Mar 30 '15 at 09:33

MySQL case execute a query on the basis of result returned from a query

2 Answers2