CODEWITHSUNDEEP
mysql
Rikaelus
Rikaelus

Reputation: 627

INSERT w/ SELECT Subquery and Duplicate Key Error

I'm working with a job queue that will have many schedulers and workers tapping into it, with some schedulers even trying to queue the same job. I want to make sure the same job isn't added if it's already there with a "pending" status.

The table has a unique ID field for code-generated UUIDs. A distinct job is identified by its name and parameters.

INSERT INTO job (id,name,parameters)
SELECT '3aa39ed8-bac8-454a-88e1-626ce6e69228', 'process_summaries', '{}'
FROM job
WHERE NOT EXISTS (
    SELECT 1 FROM job
    WHERE name = 'process_summaries'
    AND parameters = '{}'
    AND status = 'pending'
);

This is resulting in a duplicate key error on the ID, even though that ID definitely does NOT exist in the table beforehand.

Why is this happening? Is there a way to compensate?

Edit: Since nobody seems to believe me..,

mysql> SELECT * FROM job WHERE id='3aa39ed8-bac8-454a-88e1-626ce6e69228';
Empty set (0.00 sec)

mysql> INSERT INTO job (id,name,parameters)
    -> SELECT '3aa39ed8-bac8-454a-88e1-626ce6e69228', 'process_summaries', '{}'
    -> FROM job
    -> WHERE NOT EXISTS (
    ->     SELECT 1 FROM job
    ->     WHERE name = 'process_summaries'
    ->     AND parameters = '{}'
    ->     AND status = 'pending'
    -> );
ERROR 1062 (23000): Duplicate entry '3aa39ed8-bac8-454a-88e1-626ce6e69228' for key 'PRIMARY'

mysql> SELECT * FROM job WHERE id='3aa39ed8-bac8-454a-88e1-626ce6e69228';
Empty set (0.00 sec)

Upvotes: 0

Views: 48

Answers (1)

Paul Spiegel
Paul Spiegel

Reputation: 31792

If the subquery returns no row, the NOT EXISTS (...) expression will return TRUE (or 1). In that case the query will try to insert as much rows (with the same constant values) as do exist in the job table, thus raising a duplicate key error for the id column. All you need to change is to select from any one-row-table. In MySQL you can use FROM dual or a subquery (select 1). So your query could be:

INSERT INTO job (id,name,parameters)
SELECT '3aa39ed8-bac8-454a-88e1-626ce6e69228', 'process_summaries', '{}'
FROM dual
WHERE NOT EXISTS (
    SELECT 1 FROM job
    WHERE name = 'process_summaries'
    AND parameters = '{}'
    AND status = 'pending'
);

You can also just wrap your values in a subquery (one-row derived table):

INSERT INTO job (id,name,parameters)
SELECT *
FROM (SELECT '3aa39ed8-bac8-454a-88e1-626ce6e69228', 'process_summaries', '{}') sub
WHERE NOT EXISTS (
    SELECT 1 FROM job
    WHERE name = 'process_summaries'
    AND parameters = '{}'
    AND status = 'pending'
);

Upvotes: 1

Related Questions