INSERT if related row does not exist

Question

INSERT IGNORE doesn't work because there won't actually be a key conflict.

This is for a progress queue. For one application I don't want two rows with status S (for "started"). However other applications should be able to - in particular if I want to force two work items to happen at once, I can. So that's why it's not a database constraint.

So it's a little tricky to say in SQL "insert where does not exist." This works:

insert into user_queue_google
(user_id, status, start_timestamp)
(select 221, 'S', NOW() FROM filter_type
WHERE 221 not in (select user_id from user_queue_google where status = 'S') limit 1);

The problem is filter_type is a completely unrelated table that I just know is small and happens to never be empty. If it were empty for some reason, this would fail.

Can I avoid this very horrible hack without resorting to stored programs or IF/THEN logic in my SQL program?

possible duplicate of [MySQL Conditional Insert](http://stackoverflow.com/questions/913841/mysql-conditional-insert) — pilcrow, Oct 03 '13 at 15:47

score 0 · Answer 1 · answered Oct 03 '13 at 15:33

0

use the dual system dummy table

insert into user_queue_google (user_id, status, start_timestamp)
select 221, 'S', NOW()
from dual
WHERE not exists 
(
   select user_id 
   from user_queue_google 
   where status = 'S' 
   and user_id = 221
)

You are permitted to specify DUAL as a dummy table name in situations where no tables are referenced

Source

answered Oct 03 '13 at 15:33

juergen d

201,996
37
293
362

What about this bit in the doc - " MySQL may ignore the clauses." How bad is that? – djechlin Oct 03 '13 at 15:38

score 0 · Answer 2 · answered Oct 03 '13 at 15:37

You need to have a UNIQUE index for INSERT IGNORE to work effectively. What you could do is add an additional column that defaults to something harmless but could be changed to unique the key:

CREATE UNIQUE INDEX index_block ON user_queue_google (user_id, force);

Set force as a column that has DEFAULT 0. If you want to force two jobs to run at the same time, set it to something else to avoid a conflict:

UPDATE user_queue_google SET status='S', force=UNIX_TIMESTAMP() WHERE user_id=221

That'll free up a new slot for inserting new jobs.

score 0 · Answer 3 · answered Oct 03 '13 at 16:25

This should work:

insert into user_queue_google
(user_id, status, start_timestamp)
select
    A.user_id,
    A.status,
    A.start_timestamp
FROM
  (SELECT
     221 user_id, 
     'S' status, 
     NOW() start_timestamp) AS A
  LEFT JOIN user_queue_google ON A.user_id = user_queue_google.user_id AND A.status = user_queue_google.status
WHERE
   user_queue_google.user_id IS NULL;

See attached fiddle Insert if row doesn't exist

INSERT if related row does not exist

3 Answers3