inanzzz | Insert a new record with INSERT IGNORE otherwise update with ON DUPLICATE KEY UPDATE

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)