. Advertisement .
. Advertisement .
Similar to how you can create a file if not exists, it is also possible to perform “Insert If Not Exists” in MySQL and add more rows to your program. Check out these two methods for the best outcomes!
How to Perform “Insert If Not Exists” in MySQL
Method 1: Use “Insert Ignore”
The “Insert Ignore” statement in MySQL is a popular function. Most people employ “Insert Ignore” to shift an error thrown by MySQL into your warning. Hence, your query execution will not undergo any interruption.
Let’s look at an example. Suppose we establish a user table with these data:
| user_id | first_name | last_name | +---------+------------+-----------+ | 202 | Thor | Odinson | | 204 | Loki | Laufeyson | +---------+------------+-----------+ 2 rows in set (0.00 sec)
In the example above, our “user_id” column serves as the PRIMARY KEY factor. Therefore, MySQL will yield a mistake if you attempt to add new rows via the pre-existing user_id value. Our query, in this case, employs the 202’s user_id value:
INSERT INTO users (user_id, first_name, last_name) VALUES (202, "Tony", "Smith");
As a result, MySQL will generate this error:
ERROR 1062 (23000): Duplicate entry '202' for key 'users.PRIMARY'
The “Insert Ignore” function will keep MySQL staying still once your insertion adds an error. And if there is no error, the system will add new rows to your table.
Suppose you decide to operate this query:
INSERT IGNORE INTO users (user_id, first_name, last_name) VALUES (202, "Tony", "Smith"); MySQL will give you this input: Query OK, 0 rows affected, 1 warning (0.00 sec)
With this “Insert Ignore” function, MySQL will add a new row only when those values do not exist in your table.
Method 2: Use “Replace”
Suppose you want to replace all the rows where your “Insert” commands might yield errors (attributable to the duplication of Primary key or Unique values). In that case, we suggest you employ a “Replace” statement.
Once you decide to opt for “Replace”, two potential outcomes might occur for each command:
Outcome 1: No preset data row can be identified with similar values. Thus, the system will perform a typical “Insert” statement.
Outcome 2: You can find one matching data, which deletes your existing row via the regular “Delete” statement. Once done, it will perform an “Insert” function afterward.
Let’s look at an example. In this case, we may use “Replace” to switch the existing record “Id = 1” from Marcel Proust’s “In Searches of Lost Time” with Dr. Seuss’ “Green Eggs and Hams”:
mysql> REPLACE INTO books (id, title, author, year_published) VALUES (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960); Query OK, 2 rows affected (0.00 sec)
One thing to remember: though only one row got altered, the outcome implied that two rows suffered from impact. That is because we had deleted our existing row before inserting a new one to replace that.
Please refer to MySQL’s official document to understand more about “Replace”.
Our guide has shown you how to perform “Insert If Not Exists” in MySQL. Feel free to write to us with any lingering questions or uncertainty. ITtutoria hopes that this insightful instruction will streamline your programming task!