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.  We wish to insert a new row if primary/unique key or keys combination doesn’t exist. Otherwise, update the record if exists in such table.

So here I’m mentioning query with an example to perform MySQL insert row if not exists else update record. If you’re using Codeigniter, you can attend the same using Active Record as written in the article.

Here we have user_earnings table with user_id as the primary key. Also, we wish to add a new row if a particular user_id doesn’t exist. Or 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 by 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 the 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 the record in MySQL table.

You Might Interested In

Leave a Reply

Enclose a code block like: <pre><code>Your Code Snippet</code></pre>.