How to merge INSERT and SELECT operations in single MySQL statement for automation tool

[miar]

I’m working on an automation workflow that needs to add a record to a MySQL database only if a specific identifier doesn’t already exist. The challenge is that my automation platform requires a single query that returns actual data.

Currently I’m using this approach:

INSERT IGNORE INTO customers(username, encrypted_pass)
VALUES ('[email protected]', 'hashed_secret_123')

This works fine for preventing duplicates based on the username field, but the INSERT statement doesn’t return any meaningful data. My automation tool expects to receive some kind of result set to continue processing.

I found that running two separate commands works locally:

INSERT IGNORE INTO customers(username, encrypted_pass) 
VALUES ('[email protected]', 'hashed_secret_123');
SELECT * FROM customers WHERE username = '[email protected]';

However, my automation platform only accepts single queries, not multiple statements. Is there a way to create one MySQL command that both inserts the record (if it doesn’t exist) and returns the final row data? I need to get back the actual database record regardless of whether it was just inserted or already existed.

[John_Clever]

I had the same issue building automated data pipelines. Just use a single SELECT with a subquery that handles the insert:

SELECT * FROM customers WHERE username = '[email protected]' 
UNION ALL
SELECT * FROM (
    INSERT INTO customers(username, encrypted_pass) 
    SELECT '[email protected]', 'hashed_secret_123'
    WHERE NOT EXISTS (SELECT 1 FROM customers WHERE username = '[email protected]')
) AS inserted_row
LIMIT 1;

This tries the conditional insert first, then returns either the existing record or the new one. Your automation tool gets a proper result set either way. I’ve used this with several workflow engines that have the same single-query restrictions. Just check your MySQL version supports this syntax - older versions need different approaches.

[amelial]

try INSERT … ON DUPLICATE KEY UPDATE using CONNECTION_ID(). like INSERT INTO customers(username, encrypted_pass) VALUES ('[email protected]', 'hashed_secret_123') ON DUPLICATE KEY UPDATE username=username; SELECT * FROM customers WHERE username = '[email protected]'; it won’t run as two stmts, but wrap it for single query use.

[FlyingLeaf]

You can achieve this with a stored procedure to manage both the insertion and retrieval of the data. However, if you are limited to a single query, consider using the INSERT … ON DUPLICATE KEY UPDATE statement alongside LAST_INSERT_ID(). This would allow you to insert or update the record based on the unique username constraint. By doing so, you can then select the data in a single statement. Alternatively, you could use a derived table within a UNION statement that integrates both your insert logic and the subsequent select command to return the necessary output for your automation tool.