MySQL insert row if not exists else update record

Sometimes  we create MySQL tables which don’t contain auto increment primary key column instead they may have unique key or combination of unique indexes.  In such tables we wish to insert a new row if primary/unique key or keys combination doesn’t exists and update record if exists. So here I’m mentioning query with example to perform MySQL insert row if not exists else update record.

Here we have user_earnings table with user_id as primary key and we wish to add a new row if a particular user_id doesn’t exist and update the earning column value if the record already exists. Here INSERT … ON DUPLICATE KEY UPDATE clause is quite handy and we can write the query as:

The query actually checks primary keys, unique indexes and auto increment columns and performs one operation either one of below:

  • Insert a new row with 25 and 0 as user_id and earning fields values respectively or
  • Update row with adding 100 to earning field value if user_id 25 already exists.

A more sophisticated example using PHP and PDO is below:

MySQL insert row if not exists else update record 

This PDO statement will update record if a combination of user_id and product_code exists by adding supplied quantity to existing quantity and updating added_on field, otherwise will add a new row with given values. This is the way to insert row if not exists else update record in MySQL table.

You Might Interested In