Home >Database >Mysql Tutorial >How to Perform a Conditional INSERT in MySQL to Ensure Unique Rows?
MySQL Conditional Insert: Inserting Only if Unique
MySQL provides limited support for conditional inserts. In this specific case, you aim to insert a row into the x_table only if the combination of user and item doesn't exist.
MySQL's INSERT statement doesn't allow direct conditional insertion. However, you can employ a workaround using a subquery. One approach is to use the NOT EXISTS clause:
INSERT INTO x_table (instance, user, item) SELECT 919191, 123, 456 FROM dual WHERE NOT EXISTS ( SELECT * FROM x_table WHERE user = 123 AND item = 456 );
In this query, the subquery with NOT EXISTS checks if the specified user and item already exist in the x_table. If they don't, the new row will be inserted. Otherwise, nothing happens.
Another option is to utilize the MERGE statement, which allows specifying conditional insertion and update operations in a single statement. However, MERGE is supported in MySQL only as an extension, not as part of the core functionality.
The above is the detailed content of How to Perform a Conditional INSERT in MySQL to Ensure Unique Rows?. For more information, please follow other related articles on the PHP Chinese website!