MySQL: Insert with conditional

Question

I must perform the following situation down, but when you run got the error:

SQL 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 'INTO produto_seriais(serial_id) VALUES( SELECT id FROM seriais WHERE serial =' at line 5

SELECT CASE WHEN (
    SELECT COUNT(id) FROM seriais WHERE serial = '2020'
) > 1
THEN
    (INSERT INTO produto_seriais(serial_id) VALUES(
        SELECT id FROM seriais WHERE serial = '2020'
    ))
ELSE (
    INSERT INTO seriais (serial) VALUE('2020');
    SET @last_id_in_table1 = LAST_INSERT_ID();
    INSERT INTO produto_seriais (serial_id) VALUES (@last_id_in_table1);
)
END;

The case is as follows:

I'll get in "serial" table by serial "X". If it already exists, unless your ID in the "produto_seriais" table. If there is (serial), I will save the same, recover your ID and save to "produto_seriais". Any suggestions for how to do this?

Important Note: This routine will run will be thousands of times each execution (10,000 or more, depending on the quantity of serial).

P.s.: Sorry for my bad english.

You can use if,but there is a difference between if as a function and if as a branching control.The later can only be used in procedures and triggers — Mihai, Oct 17 '15 at 18:53
Your SQL doesn't make sense. Please explain what you want to do. Sample data and desired results can really help. — Gordon Linoff, Oct 17 '15 at 18:55
you can use INSERT INTO SELECT directly,if there are no rows nothing will be inserted, if they are than its ok.The ELSE part makes no sense since you are inserting a value that you already checked that it exists in the first part.You probably want an after insert trigger which will insert the foreign key into the child after each insert into the parent — Mihai, Oct 17 '15 at 19:00
Better to create stored procedure for same than it should be work — Pankaj Gupta, Oct 17 '15 at 19:07

score 0 · Answer 1 · edited May 23 '17 at 11:58

0

You could use the if exists..else statement.

If exists (select * from ....)
Begin
      Insert into ... Select id from table
End
Else
Begin
      Insert.. 
End

Please fill .... with your statements. You could use the link here to convert it for MySQL. Convert if exists for MySQL

edited May 23 '17 at 11:58

Community

1
1

answered Oct 17 '15 at 19:00

Vivek Viswanathan

1,968
18
26

This can be performed directly or in a procedure? I tried directly and returned error Erro SQL (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 'If exists (select id from seriais where serial = '2020') Begin Insert in' at line 1 * – Danilo Miguel Oct 17 '15 at 19:04
@Danilo The link I attached in the answer would help to convert to MySQL specific syntax. It has an example too. – Vivek Viswanathan Oct 17 '15 at 19:06

score 0 · Accepted Answer · answered Oct 17 '15 at 19:07

Try a stored procedure

DELIMITER //
 CREATE PROCEDURE sp_produto_seriais(IN `p_serial_id`)
    IF EXISTS (SELECT * FROM  seriais WHERE serial = p_serial_id )
    BEGIN
        INSERT INTO produto_seriais (serial_id)
        SELECT id
        FROM   seriais
        WHERE  serial = p_serial_id
    END
    ELSE
    BEGIN
        INSERT INTO seriais (serial) VALUE(p_serial_id);
        INSERT INTO produto_seriais (serial_id) VALUES (LAST_INSERT_ID());
    END //
 DELIMITER ;

usage:

CALL sp_produto_seriais('2020')

I needed only to make some minor adjustments to my case, but it worked perfect. Thank you @meda — Danilo Miguel, Oct 17 '15 at 19:37

MySQL: Insert with conditional

2 Answers2