MySQL – Insert/On Duplicate Key Update

Background

There is a slight ditch one can fall into in MySQL.

Thankfully it is easy to address.

Error

Error Message

Text

Error occurred during SQL script execution

Reason:
SQL Error [1062] [23000]: Duplicate entry '2-3-1' for key 'order.unique_Sales_Order_CustomerID_ProductID_numberofEntries'

Image

sql.error.1062.01.20200811.0924PM

Entity Diagram

entityDiagram.01.20200811.0940PM

Table Definition

SQL


create table if not exists `sales`.`order`
(
      id          bigint    not null
                  AUTO_INCREMENT

    , customerID  int not null

    , productID   int not null

    , numberofEntries tinyint not null
                  default 1

    , dateAdded     datetime      not null
                    DEFAULT CURRENT_TIMESTAMP

    , primary key
      `PK_Sales_Order`
      (
        id
      )    

    , constraint unique_Sales_Order_CustomerID_ProductID_numberofEntries
      unique
      (
          customerID
        , productID
        , numberofEntries
      )

)
ENGINE=INNODB
;

Code

Offending Code

SQL

    insert into `sales`.`order`
    ( customerID, productID, numberofEntries )
    values
        (1, 1, 1)
      , (2, 1, 1)
      , (2, 2, 1)
      , (2, 3, 1)
      , (2, 3, 1)
    ;

Clean up Code

SQL


    insert into `sales`.`order`
    ( customerID, productID, numberofEntries )
    values
        (1, 1, 1)
      , (2, 1, 1)
      , (2, 2, 1)
      , (2, 3, 1)
      , (2, 3, 1)
      on duplicate key update
        `numberofEntries` = values (`numberofEntries`) +1
    ;

Output

sql.resultSet.01.20200811.0930PM

One thought on “MySQL – Insert/On Duplicate Key Update

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s