MySQL UPDATE, IF, INSERT INTO statement

Question

I am trying to follow the following blog and use an UPDATE, IF, INSERT INTO statement seeing that it will no go over my data twice.

Please note it is for php.

The statement that I have is as follows

$query = "UPDATE 
            " . $this->table_name2 . "
        SET  
            batch = :batch, 
            created = :created
        WHERE 
            id = :id
        IF row_count() = 0 
        INSERT INTO " . $this->table_name2 . "
        SET 
            id=:id, 
            batch=:batch, 
            created=:created";

But my return value always comes back as false, and I do not know where the problem is.

If I try the first half of the statement it updates the information:

$query = "UPDATE 
            " . $this->table_name2 . "
        SET  
            batch = :batch, 
            created = :created
        WHERE 
            id = :id

And if I try the second half of the statement it INSERTS the information:

        INSERT INTO " . $this->table_name2 . "
        SET 
            id=:id, 
            batch=:batch, 
            created=:created";

I do not find any help after allot of searching so far, and I feel that somehow my IF statement may not be correct, but I do not even get info on the IF row_count() = 0 in the docs.

If can only be used in stored procedures, not as part of SQL statements — JochenJung
– JochenJung, Commented Oct 24, 2017 at 8:26
@ JochenJung O ok, there is something else that I have to learn then. What alternative statement would you suggest I use that will be the quickest if you don't mind? — Alfa Bravo
– Alfa Bravo, Commented Oct 24, 2017 at 8:33
You are maybe looking for the on duplicate key update version of the insert statement. — P.Salmon
– P.Salmon, Commented Oct 24, 2017 at 8:44

JochenJung · Accepted Answer · 2017-10-24 11:46:41Z

1

The IF statement is available in stored procedures only.

If id is the primary key (or unique) this should work for you:

INSERT INTO " . $this->table_name2 . "
       SET
            id = :id, 
            batch = :batch, 
            created = :created
ON DUPLICATE KEY UPDATE
            batch = :batch, 
            created = :created

This will execute the INSERT statement only, of the ID does not exits yet. If it already exists, it will execute the UPDATE part.

edited Oct 24, 2017 at 11:46

answered Oct 24, 2017 at 9:01

JochenJung

7,24312 gold badges67 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alfa Bravo Over a year ago

That is exactly what I was looking for, thank you very much!!

Alfa Bravo Over a year ago

Hi JochenJung, I just changed the quotes after the first created = :created to after the second created = :created to make it work. Regards

JochenJung Over a year ago

You are right, they do not belong there. Just fixed it in my answer

Collectives™ on Stack Overflow

MySQL UPDATE, IF, INSERT INTO statement

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related