Getting MySQL_Insert_Id() While Using on Duplicate Key Update with PHP

getting mysql_insert_id() while using ON DUPLICATE KEY UPDATE with PHP

Here's the answer, as suggested by Alexandre:

when you use the id=LAST_INSERT_ID(id) it sets the value of mysql_insert_id = the updated ID-- so your final code should look like:

<?
    $query = mysql_query("
        INSERT INTO table (column1, column2, column3) 
        VALUES (value1, value2, value3) 
        ON DUPLICATE KEY UPDATE
            column1 = value1, 
            column2 = value2, 
            column3 = value3, 
            id=LAST_INSERT_ID(id)
    ");
    $my_id = mysql_insert_id();

This will return the right value for $my_id regardless of update or insert.

Can I use ON DUPLICATE KEY UPDATE with an INSERT query using the SET option?

I've used ON DUPLICATE KEY UPDATE a lot. For some situations it's non-standard SQL extension that's really worth using.

First, you need to make sure you have a unique key constraint in place. The ON DUPLICATE KEY UPDATE function only kicks in if there would've been a unique key violation.

Here's a commonly used format:

 $query = "INSERT INTO $table (column1, column2, column3)
 VALUES ('value-1', 'value-2', 'value-3')
 ON DUPLICATE KEY UPDATE
 column1 = values(column1),
 column2 = values(column2),
 column3 = values(column3);"

column1 = values(column1) means "Update column1 with the value that would have been inserted if the query hadn't hit the duplicate key violation." In other words, it just means update column1 to what it would've been had the insert worked.

Looking at this code, it doesn't seem correct that you're updating all three of the columns you're trying to insert. Which of the columns has a unique constraint on it?

EDIT: Modify based on 'SET' format of mysql insert statement per the question from the OP.

Basically to use ON DUPLICATE KEY UPDATE, you just write the insert statement as you normally would, but add the ON DUPLICATE KEY UPDATE clause tacked onto the end. I believe it should work like this:

INSERT INTO $table 
    set column1 = 'value-1',
        column2 = 'value-2',
        column3 = 'value-3'
ON DUPLICATE KEY UPDATE
    column1 = values(column1),
    column2 = values(column2),
    column3 = values(column3);

Again, one of the columns you're inserting has to have a unique index (or a combination of the columns). That can be because one of them is the primary key or because there is a unique index on the table.

MySQL INSERT ....ON DUPLICATE UPDATE - Adds one to the autoincrement

INSERT ... ON DUPLICATE KEY UPDATE is described as a "mixed-mode insert" for the purposes of InnoDB's AUTO_INCREMENT handling. Mixed-mode inserts are basically ones where the maximum number of required AUTO_INCREMENT values is known, but the amount that will actually be needed is not.

Mixed-mode inserts get handled specially by default, as described in the MySQL docs:

...for “mixed-mode inserts”... InnoDB will
allocate more auto-increment values than the number of rows to be
inserted. However, all values automatically assigned are consecutively
generated (and thus higher than) the auto-increment value generated by
the most recently executed previous statement. “Excess” numbers are
lost.

If you're using InnoDB, your alternatives are:

1. Avoid INSERT ... ON DUPLICATE KEY UPDATE.
2. Set the innodb_autoinc_lock_mode parameter to 0, for "traditional" autoincrement lock mode, which guarantees that all INSERT statements will assign consecutive values for AUTO_INCREMENT columns. However, this is accomplished by locking during the statement, so there's a performance loss associated with this setting.
3. (Recommended) Ignore the gaps in the AUTO_INCREMENT column.

Note: AUTO_INCREMENT handling is totally different under MyISAM, which does not exhibit this behavior.

Only update changed values for ON DUPLICATE KEY UPDATE

I recommend you use the special VALUES() function to reference the value that would have been inserted into a column (if the insert had succeeded).

You can use an expression that compares the current value of the column to the newly provided value, and conditionally return either the current value of the column, or a new expression.

For example:

            INSERT INTO admin_sites2 (site_id,created_at,active)
            VALUES ('$key',now(),'$value')
            ON DUPLICATE KEY
            UPDATE updated_at = IF(active=VALUES(active),updated_at,NOW()),
            active = VALUES(active)

The expression IF(a,b,c) works like this: it evaluates a as a boolean; IF a is TRUE then return b else return c.

In the example above, if the current value of the active column is the same as the new value being inserted/assigned, the IF() expression returns the current value of the updated_at column. (That means that the value of the updated_at column won't be changed, since it's assigning the value that's already stored.) Otherwise, the value of NOW() will be assigned to the updated_at column.

I believe it's important to do the check of the current value of the active column BEFORE the assignment is made to the active column. (Note that the assignment to updated_at appears BEFORE the assignment to active.)

This is because of the way MySQL processes values in SQL. MySQL doesn't maintain a consistent snapshot view of the row ('at the start of the statement'). After a new value is assigned to a column, later references to that column will return the newly assigned value rather than the value it was before the statement started making changes to the row.

PDO lastInsertId() returns 0 when using on duplicate key update?

There are many places to find the answer on this, but the specificity to PDO may be hindering your search results...

First, make sure to add id=LAST_INSERT_ID(id) to the ON DUPLICATE clause, as noted at the bottom of the DOCS. The id in this case is the column name of your primary key (so in your case it may/may not be titled id).

Also, you may have to specify a sequence object argument to ->lastInsertId() to make it work. I've encountered that problem in certain circumstances before.

insert on duplicate key update

I believe the answer to this is addressed in the MySQL docs:

If a table contains an AUTO_INCREMENT column and INSERT ... UPDATE inserts a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. If the statement updates a row instead, LAST_INSERT_ID() is not meaningful. However, you can work around this by using LAST_INSERT_ID(expr). Suppose that id is the AUTO_INCREMENT column. To make LAST_INSERT_ID() meaningful for updates, insert rows as follows:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;

MySQL: Get results.insertId ON DUPLICATE KEY UPDATE

Your problem is that LAST_INSERT_ID doesn't know whether you are referring to id from the tag table or from the tag_type table. You just need to qualify the field name with its table:

let sql = `INSERT INTO tag (name, type)
           SELECT '${tag.name}', id FROM tag_type WHERE type='${tag.type}' 
           ON DUPLICATE KEY UPDATE count = count + 1, id=LAST_INSERT_ID(tag.id)`

---

Related Topics