mysql query INSERT INTO in a conditional statements (case if/else)

Question

I'm trying to figure out to insert into using conditional statements in mysql like INSERT INTO in a CASE statement

SET @foo = 1
SELECT (CASE WHEN 1 THEN (
  INSERT INTO table1 (`id`, `column`) VALUE (1, 'a');
  SET @bar = SELECT LAST_INSERT_ID();
  UPDATE table2 SET c1 = @foo WHERE c2 = @bar
) END)

I'm using case as recommended by How do write IF ELSE statement in a MySQL query

But when I try that I keep getting:

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 'INTO ForgeRock (`id`, `productName`, `description`) VALUE (4, 'a','b') ) END)' at line 2

http://sqlfiddle.com/#!9/dcb16/22063

So I'm trying to go with the if/else approach...

SELECT IF(1,(INSERT INTO ForgeRock (`id`, `productName`, `description`) VALUE (4, 'a','b')),0)

But that gives the same error

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 'INTO ForgeRock (`id`, `productName`, `description`) VALUE (4, 'a','b')),0)' at line 1

http://sqlfiddle.com/#!9/dcb16/22081

So I'm assuming this is not possible to do in mysql? https://stackoverflow.com/a/13681306/1267259 (OP is however not getting the same error?)

As I was writing this question I found Using IF ELSE statement based on Count to execute different Insert statements Is the only way to do this by using procedure?

Yes,flow control statements can only be done in stored programs in mysql — Mihai, Oct 24 '15 at 06:18

wchiquito · Accepted Answer · 2015-10-24T07:35:14.083

Remember the difference between IF() function and IF statement.

One option you can implement is to use a FUNCTION (13.1.12 CREATE PROCEDURE and CREATE FUNCTION Syntax):

/* CODE FOR DEMONSTRATION PURPOSES */

DELIMITER //

CREATE FUNCTION `insert_new_value` (`p_foo` INT UNSIGNED) RETURNS TINYINT(1)
DETERMINISTIC
BEGIN
  /*
  INSERT INTO table1 (`id`, `column`) VALUE (1, 'a');
  SET @bar = SELECT LAST_INSERT_ID();
  -- UPDATE table2 SET c1 = @foo WHERE c2 = @bar;
  UPDATE table2 SET c1 = `p_foo` WHERE c2 = @bar;
  */
  INSERT INTO `ForgeRock`
  (`id`, `productName`, `description`)
  VALUES
  (4, 'a', 'b');
  RETURN 1;
END//

DELIMITER ;

SET @foo := 1;
-- You can also use CASE expression.
SELECT IF(1, `insert_new_value`(@foo), 0) INTO @`return_value`;

/* CODE FOR DEMONSTRATION PURPOSES */

SQL Fiddle demo

Function/procedure are both too complicated for my case but +1 and accepted as a good example of how to do it. — user1267259, Oct 24 '15 at 16:03

mysql query INSERT INTO in a conditional statements (case if/else)

1 Answers1