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.
1 2 3 4 5 6 7 8 9 10 | CREATE TABLE IF NOT EXISTS `user_earnings` ( `user_id` int(6) NOT NULL, `earning` int(6) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Indexes for table `user_earnings` -- ALTER TABLE `user_earnings` ADD PRIMARY KEY (`user_id`); |
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:
1 2 | INSERT INTO `user_earnings` (`user_id`, `earning`) VALUES(25, 0) ON DUPLICATE KEY UPDATE `earning`=VALUES(`earning` + 100) |
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_idandearningfields values respectively or - Update row by adding 100 to
earningfield value ifuser_id25 already exists.
A more sophisticated example using PHP and PDO is below:
MySQL insert row if not exists else update record
1 2 3 4 5 6 7 8 9 10 11 12 13 | $user_id = 99; $product_code = 'MKCC02156N'; $qty = 3; $added_on = date('Y-m-d H:i:s'); //user_id and product_code as unique combination of keys // query $sql = "INSERT INTO user_earnings (user_id, product_code, quantity, added_on) VALUES (?,?,?,?) ON DUPLICATE KEY UPDATE quantity=(quantity + VALUES(quantity)), added=VALUES(added_on)"; $q = $conn->prepare($sql); $q->execute(array($user_id, $product_code, $qty, $added_on)); |
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.