MySQL – Insert/Ignore

Background

In our last post, we touched on a duplicate key error.

Here is another quick workaround around the same issue.

Lineage

  1. MySQL – Insert/On Duplicate Key Update
    Link

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


start transaction;

    delete from `sales`.`order` where id >0;
    delete from `sales`.`product` where productid >0;
    delete from `sales`.`customer` where customerID >0;

    insert into `sales`.`customer`
    (customerID, customerName)
    values
        (1, 'UPS')
      , (2, 'Fedex')
    ;

    insert into `sales`.`product`
    ( productID, productCode, productName )
    values
        (1, 'UPS', 'UPS')
      , (2, 'Fedex', 'Fedex')
      , (3, 'Airborne', 'Airborne')

      ;

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

    select *
    from    `sales`.`order`
    ;

commit
;

Clean up Code

Outline

  1. Add Ignore clause to insert statement

SQL


start transaction;

    delete from `sales`.`order` where id >0;
    delete from `sales`.`product` where productid >0;
    delete from `sales`.`customer` where customerID >0;

    insert into `sales`.`customer`
    (customerID, customerName)
    values
        (1, 'UPS')
      , (2, 'Fedex')
    ;

    insert into `sales`.`product`
    ( productID, productCode, productName )
    values
        (1, 'UPS', 'UPS')
      , (2, 'Fedex', 'Fedex')
      , (3, 'Airborne', 'Airborne')

      ;

    -- Add ignore clause to insert statement
    insert ignore into `sales`.`order`
    ( customerID, productID, numberofEntries )
    values
        (1, 1, 1)
      , (2, 1, 1)
      , (2, 2, 1)
      , (2, 3, 1)
      , (2, 3, 1)
    ;

    select *
    from    `sales`.`order`
    ;

commit
;

Output

Output Results

Grid

sql.warning.1062.01.20200812.1109AM

Textual

4 row(s) affected, 1 warning(s): 1062 Duplicate entry '2-3-1' for key 'order.unique_Sales_Order_CustomerID_ProductID_numberofEntries' 

Records: 5  Duplicates: 1  Warnings: 1	0.000 sec

Output Result

Grid

sql.resultSet.01.20200812.1115AM

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