MySQL insert-Statement double entries

Question

I want to create a query that inserts data to a table and checks if my date range overlaps the already existing date ranges.

I created the following working query but it inserts 2 rows with the same data. Can you find anything wrong in it?

INSERT INTO infoscreen_times (screen_id, start, end, view_id)
SELECT ?, ?, ?, ?
FROM infoscreen_screens
WHERE EXISTS (
    SELECT id
    FROM infoscreen_times
    WHERE ? BETWEEN start AND end
    AND screen_id = ?
    OR ? BETWEEN start AND end
    AND screen_id = ?
    OR ? <= start
    AND ? >= end
    AND screen_id = ?) IS FALSE

start means the Start-Timestamp, end is the End-Timestamp. In the Subquery i want the check if the timestamps i want to insert overlap each other. If they do i want to get a error and if they dont the query insert the data.

Thanks in advance.

you're mixing `and` and `or` without any `()` to enforce parsing order. that's almost always a sign of a bug. — Marc B, May 26 '16 at 15:00
I think it is better use programing language to moderate your query — SayJeyHi, May 26 '16 at 15:09

score 1 · Answer 1 · answered May 26 '16 at 15:33

I believe this is the logic you want. Most importantly, you want not exists. Secondarily, you need to get the date arithmetic correct:

INSERT INTO infoscreen_times (screen_id, start, end, view_id)
    SELECT ?, ?, ?, ?
    FROM infoscreen_screens iss
    WHERE NOT EXISTS (SELECT 1
                      FROM infoscreen_times ist
                      WHERE ist.screen_id = iss.screen_id AND
                            ? <= ist.end AND -- ? is the begin time
                            ? >= ist.begin   -- ? is the end time
                     );

score 0 · Answer 2 · edited May 23 '17 at 11:44

0

You need an SQL expression that generates a boolean depending on whether or not one date range overlaps another. See this SO answer

What you're using does not seem correct. I can't diagnose exactly why, or suggest a better query without knowing the table schema. Can you post that?

edited May 23 '17 at 11:44

Community

1
1

answered May 26 '16 at 15:02

Charles Bretana

143,358
22
150
216

score 0 · Answer 3 · answered May 26 '16 at 15:06

You should use parentheses to distinct different parts of OR operators. I think the answer is:

INSERT INTO infoscreen_times (screen_id, start, end, view_id) 
SELECT ?, ?, ?, ? 
FROM infoscreen_screens 
WHERE EXISTS (SELECT id FROM infoscreen_times WHERE (? BETWEEN start AND end AND screen_id = ?) OR (? BETWEEN start AND end AND screen_id = ?) OR (? <= start AND ? >= end AND screen_id = ?)) 
IS FALSE

However, I don't know the meaning of these question marks in MySql. :)

MySQL insert-Statement double entries

3 Answers3