Home >Database >Mysql Tutorial >How to Perform a Conditional INSERT in MySQL to Ensure Unique Rows?

How to Perform a Conditional INSERT in MySQL to Ensure Unique Rows?

Mary-Kate Olsen
Mary-Kate OlsenOriginal
2024-12-13 05:40:13243browse

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn