Conditional statements and multiple queries in mysql

Question

In MySQL (both instances of phpmyadmin and MySQL workbench) I would like to check if a value exists in a table before I perform any other additional queries to the same table or other tables and below are the sets of queries I have tried but neither are working and both give errors, am new to forming complex queries so kindly ignore any syntax errors I must have made while forming this queries

Query 1

SELECT CASE WHEN ( (SELECT * FROM likes WHERE face_id = 'mm' AND phone_id = 'pp') > 1 )
THEN ( SELECT CASE WHEN ( (SELECT * FROM dislikes WHERE face_id = 'mm' AND phone_id = 'pp') > 1) 
   THEN  ( DELETE FROM likes WHERE face_id = 'mm' AND phone_id = 'pp' )
   ELSE  SELECT * FROM likes
   END
 )
ELSE INSERT INTO likes (face_id, phone_id) VALUES ('mm', 'pp')
END

Query 2

IF EXISTS ( SELECT * FROM likes WHERE face_id = 'mm' AND phone_id = 'pp')
 IF EXISTS ( SELECT * FROM dislikes WHERE face_id = 'mm' AND phone_id = 'pp')
  DELETE FROM likes WHERE face_id = 'mm' AND phone_id = 'pp'
 ELSE
   SELECT * FROM likes
ELSE/*Else of first IF*/
INSERT INTO likes (face_id, phone_id) VALUES ('mm', 'pp')
END

I got both my choice of queries from here and here

I have 2 tables, likes and dislikes; both tables have three fields id, face_id and phone_id. Data/Values entering into the likes table must not be present in the dislikes table thus if for instance I wish to insert some values into the likes table, I would check if those values are not present in my likes table and then insert my values and return true but if it is present I would check if somehow it is also present in the dislikes table and if it is, I would delete it from the dislikes table, if not, nothing should happen.

Can you please provide your table definitions, sample data and the expected output? This code looks fairly complex and I suspect there's a simpler way to do what you want. — Patrick Tucci, Jun 15 '15 at 13:20
Why don't you combine those 2 tables into 1 and add a fourth column? A boolean that's true when it's a like, everything else is a dislike. Or where the fourth column is an enum with either LIKE or DISLIKE. Next you create a unique constraint on the first three fields. — Michaël Benjamin Saerens, Jun 15 '15 at 13:41
Hi, I may be wrong. But generally speaking you can't perform an update, Insert or Delete when running a normal query. Depending on what technology you're working with you may need to do this in a different way. In oracle I would do something in PL/SQL to perform this kind of operation. — Fudztown, Jun 15 '15 at 13:51
@MichaëlBenjaminSaerens I like your idea on combining the tables, and if combined please show me what the query would then look like. — , Jun 15 '15 at 14:37
@MichaëlBenjaminSaerens I combine the tables and i tried this `SELECT CASE WHEN ( (SELECT * FROM expressions WHERE face_id = 'mm' AND phone_id = 'pp') = 1 ) THEN UPDATE expressions SET expression = 1 WHERE face_id = 'mm' AND phone_id = 'pp' ELSE INSERT INTO expressions (face_id, phone_id, expression) VALUES ('mm', 'pp', 1) END` but no luck, gives an error @Fudztown could you jelp out here. Thanks — , Jun 15 '15 at 15:08
@Fudztown This is the 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 'UPDATE expressions SET expression = 1 WHERE face_id = 'mm' AND phone_id = 'pp' E' at line 1` — , Jun 19 '15 at 16:37

score 2 · Accepted Answer · answered Jun 21 '15 at 00:33

As stated you can't perform an update, Insert or Delete when running a normal query, thus it would have to be a stored procedure in my database and i created one like so

BEGIN
IF(SELECT COUNT(*) FROM expressions WHERE face_id = face_id_ AND phone_id = phone_id_ = 1 )
THEN UPDATE expressions SET expression = express_ WHERE face_id = face_id_ AND phone_id = phone_id_;
ELSE INSERT INTO expressions (face_id, phone_id, expression) VALUES (face_id_, phone_id_, express_);
END IF;
END

and then called it in my query like so using PDO

SET SQL_SAFE_UPDATES = 0;
call update_expressions(:pid, :fid, :exp);

score 0 · Answer 2 · answered Jun 15 '15 at 14:05

No much to go on here. But what about the following?

/*Two queries*/
DELETE FROM LIKES
  WHERE FACE_ID IN (SELECT FACE_ID
                      FROM LIKES a, DISLIKES b
                     WHERE     FACE_ID = 'mm'
                           AND PHONE_ID = 'pp'
                           AND a.FACE_ID = b.FACE_ID
                           AND a.PHONE_ID = b.PHONE_ID)
/
/*All likes that are not in likes*/
INSERT INTO LIKES 
SELECT FACE_ID, PHONE_ID FROM DISLIKES
MINUS
SELECT FACE_ID, PHONE_ID FROM LIKES

Conditional statements and multiple queries in mysql

2 Answers2