Chanpuru Chanpuru - 1 month ago 8
SQL Question

UPDATE date range into date columns if dates don't overlap with existing ones

A follow up question to the answer here. Since I can't comment on the answer, I can't ask directly.

I need to update a row and still have it check whether times overlap with other entries. However, I don't know how I can change the statement to exclude looking at itself during the check.

With an Insert statement like this

INSERT INTO table (name, starttime, endtime)
SELECT 'test', '2016-10-12 22:00:00', '2016-10-12 23:00:00'
FROM (SELECT 1) x
LEFT
JOIN table y
ON y.starttime < '2016-10-12 23:00:00' AND y.endtime > '2016-10-12 22:00:00'
WHERE y.id is NULL LIMIT 1;


How do I modify my UPDATE statement to do the same while excluding the row being updated from the check?

UPDATE table SET name = 'test2', starttime = '2016-10-12 22:15:00',
endtime = '2016-10-12 23:00:00' WHERE id = 1


id is the primary key, and I'm using it to identify the row

Answer

You can use not exists in both insert and update queries:

insert into a_table (name, starttime, endtime) 
select 'test', '2016-10-12 22:00:00', '2016-10-12 23:00:00'
where not exists (
    select 1 from a_table
    where starttime < '2016-10-12 23:00:00' and endtime > '2016-10-12 22:00:00'
    );

update a_table 
set name = 'test2', starttime = '2016-10-12 22:15:00', endtime = '2016-10-12 23:00:00'  
where id = 1 
and not exists (
    select 1 from a_table
    where id <> 1 
    and starttime < '2016-10-12 23:00:00' and endtime > '2016-10-12 22:15:00'
    );