INSERT ...ON DUPLICATE KEY UPDATE when key is not always known?

Question

I'm trying to update a record if the key is known else I want to insert it and get the inserted id, currently I have:

    if(isset($data['applicationId']))
    {
        //update
        $sql="
         UPDATE myTable SET data='jsonstring' WHERE id = {$data['applicationId']}
        ";
    }
    else
    {
        //insert and get id
        $sql="
         INSERT INTO myTable SET data='jsonstring'
        ";
    }

Is it possible to simplify the above to one query using INSERT ...ON DUPLICATE KEY UPDATE even when the key is not always known ?

I've tried this:

        INSERT INTO myTable
        (
            id,
            data

        )
        VALUES
        (
            ?, # <- I may not know this!!
            'jsonstring'
        )
        ON DUPLICATE KEY UPDATE 
            data = 'jsonstring'

Thanks for any suggestions.

can you please elaborate your question? the `INERT ON DUPLICATE KEY UPDATE` should work fine. i.e. if the `id` you pass is already inserted, `data` will be updated to 'jsonstring' otherwise a new record with your provided `id` will be inserted. are you getting an error that you can share? — mynawaz, Aug 29 '15 at 07:10

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

Yes, you can do that, assumed id is your primary key and auto_increment. You will have two different queries, one if you know the applicationId and one when you not knowing it.

The first, when you know it:

INSERT INTO myTable
(
    id,
    data
)
VALUES
(
    1337, # <- insert id
    'jsonstring'
)
ON DUPLICATE KEY UPDATE 
        data = 'jsonstring';

And the one if the applicationId is unknown:

INSERT INTO myTable
(
    id,
    data
)
VALUES
(
    NULL, # <- This will cause mysql to use a auto_increment value
    'jsonstring'
)
ON DUPLICATE KEY UPDATE 
        data = 'jsonstring';

So you can conclude this to:

    $sql="INSERT INTO myTable
        (
            id,
            data
        )
        VALUES
        (" .
            isset($data['applicationId']) ? $data['applicationId'] : 'NULL'
            .",
            'jsonstring'
        )
        ON DUPLICATE KEY UPDATE 
                data = 'jsonstring';
    ";

But be aware of How can I prevent SQL-injection in PHP?

Happy coding

looks promising, will try this in the morning and let you know. — angelcool.net, Aug 29 '15 at 07:55

score 0 · Answer 2 · answered Aug 29 '15 at 07:20

Please forgive because your question is not 100% clear. However, the concept I can tell is that you want to be able to ask more than 1 query on 1 sql statement. That can be done with a multi-query command. However, if you want some of your data from a query placed in your next query I do not think it will work. Link provided for multi_query

http://php.net/manual/en/mysqli.quickstart.multiple-statement.php

score 0 · Answer 3 · answered Aug 29 '15 at 09:55

First, Simple update query will run. If it runs successfully, it will not go to if condition and your ID will be the one which was used in updating.

And, if that ID is not available (means update query fails, $Query will be false), so pointer jumps to if condition and insert the query. Now, new Inserted ID we can get.

$ID=$data['applicationId'];
    $Query=mysql_query("UPDATE myTable SET data='jsonstring' WHERE id='$ID' ");
    if(!$Query)
    {
       $InsertQuery=mysql_query("INSERT INTO myTable SET data='jsonstring'");
       $ID=mysql_insert_id();
    }

    So, $ID will be your ID.(either updated or currently inserted)

INSERT ...ON DUPLICATE KEY UPDATE when key is not always known?

3 Answers3