Hello everyone!

We have been investing plenty of personal time and energy for many years to share our knowledge with you all. However, we now need your help to keep this blog running. All you have to do is just click one of the adverts on the site, otherwise it will sadly be taken down due to hosting etc. costs. Thank you.

If you want to combine two sql statements into one for inserting a new record or updating if it already exists then you can use this example. Whole logic relies on unique key index. If the user count doesn't exist in database then create one by assigning 1 visit to it otherwise increase total visit by 1.


Table


CREATE TABLE `visit` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`total_visit` varchar(45) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `user_id_UNIQUE` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

Insert Into clause


It inserts a new record if it doesn't exists otherwise completely ignores it. If we used this for the scenario above, user's total visit would always be 1 which is not what we want.


mysql> SELECT * FROM visit;
Empty set (0.00 sec)

mysql> INSERT IGNORE INTO visit (user_id, total_visit) VALUES (32, 1);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT IGNORE INTO visit (user_id, total_visit) VALUES (32, 1);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM visit;
+----+---------+-------------+
| id | user_id | total_visit |
+----+---------+-------------+
| 1 | 32 | 1 |
+----+---------+-------------+
1 row in set (0.00 sec)

On Duplicate Key Update clause


It inserts a new record if it doesn't exists otherwise updates its current total value by 1 which is what we want.


Empty set (0.00 sec)

mysql> INSERT IGNORE INTO visit (user_id, total_visit) VALUES (32, 1) ON DUPLICATE KEY UPDATE total_visit = total_visit + VALUES(total_visit);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM visit;
+----+---------+-------------+
| id | user_id | total_visit |
+----+---------+-------------+
| 1 | 32 | 1 |
+----+---------+-------------+
1 row in set (0.00 sec)

mysql> INSERT IGNORE INTO visit (user_id, total_visit) VALUES (32, 1) ON DUPLICATE KEY UPDATE total_visit = total_visit + VALUES(total_visit);
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM visit;
+----+---------+-------------+
| id | user_id | total_visit |
+----+---------+-------------+
| 1 | 32 | 2 |
+----+---------+-------------+
1 row in set (0.00 sec)