Monday, July 12, 2021

MySQL - How to insert record if not exists to avoid data duplication

While coding an application with MySQL as database, you might come into this situation:

You want to insert a new record into a table, if and only if there is no existing record in the table with certain matching fields.

  • The matching field(s) need not has a unique constrain in your table structure design.
  • Your insert statement does not include value definition for the table's autoincremented primary key (the "id"). As usual, you just let the database engine to determine the value of the autoincremented field for you upon successful insert action.
  • You only want to check for duplication in certain field(s). The rest of the fields can be the same or different. Meanings, as long as all those fields you want to check are completely matching, it is considered a duplicated record, regardless the rest of the fields are matching or not.
  • In the event you have found a duplication, you just want to do nothing and skip the insert. This is different from another situation whereby you want to update the existing record with current one.
  • You don't want to mess up with the primary key (the "id") sequence. Meanings, if there is a match, you just abort the insert action without touching the table. The "id" sequence can continue with next insert, without jumping autoincrement number.
and here is an example of the SQL statement, with value fed by PHP variables:

INSERT INTO `payment` (`year`,`month`,`item`,`category`,`type`,`account_no`,`date_due`,`value`,`payment_mode`,`paid_by`,`status`,`owner`,`payer`) SELECT $year,$month,'$item',$category,'$type','$account_no','$date_due',$value,$payment_mode,$payment_option,'$status',$owner,$payer FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `payment` WHERE `year`=$year AND `month`=$month AND `item`='$item' AND `type`='$type' AND `date_due`='$date_due' LIMIT 1)

The table structure for payment is as below:

In the example above, you match on fields year, month, item, type and date_due in the table payment. Your insert statement will be executed if the select statement return no result, i.e. there is no matching record(s) fulfilling the search criteria.


Post a Comment

Hint: Click on the "Older Posts" link to continue reading, or click here for a listing of all my past 3 months articles.