How to insert into mysql with Boolean condition

Question

I have this statement that I wanna execute:

INSERT INTO kitchenitems (kno, `date`, added_Date, added_By) 
VALUES (5,'2016-04-01', now(), 2);

but before it executes i wanna check whether "kno" has "5" or not. If not it should execute. If has it should not execute. Thanks in advance.

if the number 5 is dynamic, it must be produced by programming language (not sql). Can't you check the number in a if statement on that side? — Jonathan Clark, Apr 01 '16 at 15:40
Alternatively you could use a stored procedure if it has to be mysql that checks the "5". — Jonathan Clark, Apr 01 '16 at 15:43
that is how i do it now get it to a resultset and check. but i wanna increase the efficiency i saw somewhere that it can be done. @JonathanClark i do not get it. — Door D Uthpala, Apr 01 '16 at 15:48

score 1 · Accepted Answer · edited May 23 '17 at 11:59

1

You can do something like this:

    INSERT INTO kitchenitems (`kno`, `date`, `added_Date`,`added_By`)
    SELECT kno,date,added_Date,added_By
    FROM (SELECT '5' as kno, '2016-04-01' as date, NOW() as added_Date, '2' as added_By) a
    WHERE NOT EXISTS (SELECT 1 FROM kitchenitems b WHERE a.kno = b.kno);

However, if you want unique values of kno, you should ensure that at server-side.

edited May 23 '17 at 11:59

Community

1
1

answered Apr 01 '16 at 15:49

Sevle

3,014
2
17
28

score 0 · Answer 2 · answered Apr 01 '16 at 15:52

Hi there I think that can easealy be done with stored procedure... I would suggest you to read this article HERE I think that's the answer on your solution.

Here is SQL Fiddle to see how that works on your problem...

here is your stored procedure

CREATE PROCEDURE addItem 
(IN inkno INT, IN inddate DATETIME, IN inadded_Date DATETIME, IN inadded_By INT)
BEGIN
    DECLARE SomeId int;
    DECLARE CheckExists int;  
    SET CheckExists = 0;  

    SELECT count(*) INTO CheckExists from kitchenitems WHERE kno = inkno;   

    IF (CheckExists > 0) THEN 
        SELECT kno INTO SomeId FROM kitchenitems WHERE kno = inkno;  
    ELSE 
        INSERT INTO kitchenitems (kno,ddate,added_Date,added_By) 
        VALUES (inkno, inddate,inadded_Date, inadded_By); 
    END IF;

END/

How to insert into mysql with Boolean condition

2 Answers2