1

For MySQL, If I stored the userid and a token in the user table and only allows to insert record in another table if the supplied userid and token matches.I've tried

INSERT INTO product(description) 
VALUES('123') 
WHERE EXISTS 
  (SELECT 1 FROM users where userid='myuserid' AND token='ABCD')

The following SQL statement produces error "check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE EXISTS (SELECT 1 FROM users where userid='29')'"

However for update I can update successfully with

UPDATE product 
SET description='123' 
WHERE EXISTS 
  (SELECT 1 FROM users where userid='myuserid' AND token='ABCD')

Can any experts please help to advise. I need the most efficient way to verify the user token is correct before doing the insert.

Ken White
  • 123,280
  • 14
  • 225
  • 444
user1997296
  • 157
  • 1
  • 8
  • Possible duplicate of [MySQL INSERT IF (custom if statements)](http://stackoverflow.com/questions/6854996/mysql-insert-if-custom-if-statements) – Tomaso Albinoni Dec 17 '15 at 23:19

1 Answers1

0

Please try using a trigger instead:

CREATE TRIGGER tokenValidator 
BEFORE INSERT 
ON product
FOR EACH ROW BEGIN
IF (SELECT 1 FROM users where userid='myuserid' AND token='ABCD') THEN
  INSERT INTO product(description) VALUES('123') 
END IF
Abhishek Singh
  • 369
  • 2
  • 17
  • Still getting an error. "check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF (SELECT 1 FROM users where userid='29') THEN INSERT...". Understand that IF statement can only be used in storedprocedure – user1997296 Dec 18 '15 at 13:09