Announcement

Collapse
No announcement yet.

Why using INSERT ON DUPLICATE KEY the AUTO_INCREMENT goes itself ?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Why using INSERT ON DUPLICATE KEY the AUTO_INCREMENT goes itself ?

    Hello all,

    I got a query that insert new record is not present and update the existing ones if available.
    I got a AUTO_INCREMENT field that is the number of row.

    If I run more than one time the same query that field run always as it would be a new insert also if it's not.
    Another strange thing is that the number jumps 2 numbers (from 10 to 13 instead of 11)
    Is there a trick to avoid that behavior ??

    Code:
    $sql_insert_rows="INSERT INTO orders_details (OrderID , CustomerID, SalesmanID , OrderDate , ProductID , UnitPrice , CurrencyID , Quantity)
    SELECT OrderID , CustomerID, SalesmanID , OrderDate , ProductID , UnitPrice , CurrencyID , Quantity
    FROM orders_details_temp 
    ON DUPLICATE KEY UPDATE         
            orders_details.OrderID = orders_details_temp.OrderID ,
            orders_details.CustomerID = orders_details_temp.CustomerID,
            orders_details.SalesmanID = orders_details_temp.SalesmanID,
            orders_details.OrderDate = orders_details_temp.OrderDate,
            orders_details.ProductID = orders_details_temp.ProductID ,
            orders_details.UnitPrice = orders_details_temp.UnitPrice ,
            orders_details.CurrencyID = orders_details_temp.CurrencyID,
            orders_details.Quantity = orders_details_temp.Quantity ";
    See image to understand better
    Thanks
    Attached Files
    Last edited by Giu; 08-01-2015, 04:42 AM. Reason: format code

  • #2
    Is it safe ;-(( set AUTO_INCREMENT to "" before to run the query and then set it again to AUTO_INCREMENT ??? It seems a problem of mysql...

    ALTER TABLE ` orders_details` CHANGE `Order_row_ID` `Order_row_ID` INT( 11 ) NOT NULL

    then after the query

    ALTER TABLE ` orders_details` CHANGE `Order_row_ID` `Order_row_ID` INT( 11 ) NOT NULL AUTO_INCREMENT

    Ok... I've just tried... and the only difference is that lines number start from 1 to 5.

    Really misterious !!!!
    Last edited by giovannino; 07-30-2015, 12:11 PM.

    Comment


    • #3
      Your ON DUPLICATE ... part will never be executed because your primary key is Order_row_ID and you select
      the data to be inserted from order_details_temp and there is no Order_row_ID in your statement/order_details_temp.
      There is no colliding key and it will always be a regular insert.

      The 'jumping' auto_increment is NOT a bug or problem of MYSQL. Check your temp table and make
      sure there are no empty lines in it, do you run any other command (delete?) on the table, is it part of a transaction?

      Also auto_increment guarantees unique numbers not necessarily sequential ones. What you can do is check and set the the auto_increment value
      every time before you run the insert statement.


      jsb

      Comment


      • #4
        Hello JSB !!! You got as always the reason.
        At the end, I did this modification and now it seems to work properly....... (not really sure ..;-) )

        Code:
        $sql_insert_rows="INSERT INTO orders_details (Order_row_ID, OrderID , CustomerID, SalesmanID , OrderDate , ProductID , UnitPrice , CurrencyID , Quantity)
        SELECT (SELECT Order_row_ID FROM orders_details WHERE OrderID = t.OrderID AND 
        CustomerID = t.CustomerID AND 
        SalesmanID = t.SalesmanID AND 
        OrderDate = t.OrderDate AND 
        ProductID = t.ProductID AND 
        UnitPrice = t.UnitPrice AND
        CurrencyID = t.CurrencyID) AS Order_row_ID, 
        t.OrderID , t.CustomerID, t.SalesmanID , t.OrderDate , t.ProductID , t.UnitPrice , t.CurrencyID , t.Quantity
        FROM orders_details_temp t 
        ON DUPLICATE KEY UPDATE
        #Order_row_ID = t.Order_row_ID;
        OrderID = t.OrderID ,
        CustomerID = t.CustomerID,
        SalesmanID = t.SalesmanID,
        OrderDate = t.OrderDate,
        ProductID = t.ProductID ,
        UnitPrice = t.UnitPrice ,
        CurrencyID = t.CurrencyID,
        Quantity = t.Quantity ";
        Last edited by Giu; 08-01-2015, 04:41 AM. Reason: format code

        Comment

        Working...
        X