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?