can you begin a mysql statement with "IF"?

Question

I am trying to implement "IF record EXISTS THEN UPDATE ELSE INSERT" in mysql. Specifically, I have to figure out how to do that with Doctrine ORM.

One solution using native MySql is to use "ON DUPLICATE KEY UPDATE". This is unfortunately not supported in Doctrine.

Another semi solution is to use "REPLACE INTO" syntax. Doctrine supports this, however REPLACE doesn't UPDATE records, instead it drops the record and creates a new one (with a new auto increment id.) Because of this, it's a bad solution if you depend on auto-increment ids as I do.

The table I'm trying to operate on is INNODB, so I have looked into transactions, but I don't fully understand if the same rules apply inside of transactions as apply outside of them.

Can I say "IF(INSERT IGNORE INTO table critical_id = $id) THEN exit ELSE (UPDATE table SET field = 'new_value')" inside of a transaction?

If I can't say something like that, is there any solution to this problem?

Answer 1

You have already mentioned INSERT IGNORE INTO. Is this not meeting your needs?

http://bogdan.org.ua/2007/10/18/mysql-insert-if-not-exists-syntax.html

Answer 2

Either find a way to make "ON DUPLICATE KEY UPDATE" work, or switch to using two statements: first SELECT to find any existing rows, then UPDATE or INSERT as necessary.

That's pretty much your only option, short of building a stored proc that does the above work for you.

Answer 3

"Insert or update" is called "Upsert". If you google "upsert mysql" you will find that there are only few solutions.

1. 2-step, Insert, then Update and variations
2. "ON DUPLICATE KEY UPDATE"
3. REPLACE

And maybe few more, but most of those are variations.

Using transactions your SQL would look like this:

    UPDATE xxx SET (x1, x2) = (y1, y2) WHERE id = myid;
    IF found THEN
        RETURN;
    END IF;
    BEGIN
        INSERT INTO xxx (x1, x2) VALUES (y1, y2);
        RETURN;
    EXCEPTION WHEN unique_violation THEN
    --##Do nothing, or you can put this whole sql code
    --##in a LOOP and it will loop over and try update again
    END;

This code is stripped from one of my postgresql functions. I think InnoDB doesn't support this, but I might be wrong.