How can I conditionally insert data into a MySQL table based on specific conditions?

MySQL Conditional INSERT Statements Using SELECT

Often, you may need to insert data into a table only if certain conditions are met. While stored procedures offer one way to achieve this, let's explore how you can perform conditional inserts directly in your SQL queries.

To conditionally insert data, you can utilize the following syntax:

INSERT INTO TABLE
SELECT value_for_column1, value_for_column2, ...
FROM wherever
WHERE your_special_condition

If the subquery in the WHERE clause returns no rows because the condition is not met, no insert occurs.

Example:

Consider the following tables:

CREATE TABLE products (id INT PRIMARY KEY AUTO_INCREMENT, qty_on_hand INT);
CREATE TABLE orders (id INT PRIMARY KEY AUTO_INCREMENT, product_id INT, qty INT);

To place an order for 20 Voodoo Dolls (product ID 2), we can check if there's sufficient stock on hand and conditionally insert the order as follows:

INSERT INTO orders (product_id, qty)
SELECT 2, 20
WHERE (SELECT qty_on_hand FROM products WHERE id = 2) > 20;

If the quantity on hand is sufficient, the order row will be created; otherwise, the insert will not occur. This method helps mitigate concurrency issues by checking the stock availability and executing the insert in a single transaction.

The above is the detailed content of How can I conditionally insert data into a MySQL table based on specific conditions?. For more information, please follow other related articles on the PHP Chinese website!