I now have such a list, a2, a3 for date fields, start date, end date

I want to do now is, when I insert a record in the new time, to determine the new record, the start time and end time, isn't it ever covered in existing records, if you have covered can't insert

For example I want to insert a record date for the 2019-01-15/2019-01-15 the record date is covered in one time, so the insert is not successful,
Or the 2019-01-25/2019-01-25 is not in conformity with the insert condition, only not repeat period can be inserted,

The above how to write SQL in ORACLE

Id a1 a2, a3
1 1001 2019-01-04 2019-01-10
2 1002 2019-01-20 2019-02-10

.

CodePudding user response:

You put the a2, a3 don't set the primary key is ok

CodePudding user response:

Insert into table (a2, a3)
The select a2, a3 from table_new t
Where not the exists (select 1 from table t1 where t1. The a2 & lt;=3 and t1 t.a. A3 & gt;=t.a. 2)

CodePudding user response:

The merge into understanding once

CodePudding user response:

 insert into table (a2, a3) 
The select a2, a3 from table_new t 
Where not exists 
(select one from the table t1 
The where (t1) a2 between t.a 2 and t.a. 3) the or 
And (t1) a3 between t.a 2 and 3) t.a 
) 

CodePudding user response:

This statement is wrong,,

Now I have such a record data
But I want to insert the 2019-1-1 to 2019-1-1 this period of data, the existing database record should have this to insert the time period is overlap
But now carry out was to insert?

Select * from CUX_TEST;
Select * from CUX_TEST t1 where trunc (t1) a2) & lt;=to_date (' 2019-01-01 ', 'yyyy/mm/dd) and t1. The a3 & gt;=to_date (' 2019-03-01 ', 'yyyy/mm/dd)

CodePudding user response:

Have you found the condition (this is not his, plus go to ah,

CodePudding user response:

What do you mean? Did not understand

CodePudding user response:

refer to 7th floor cpp_1 response:

what do you mean? Don't see

Select * from CUX_TEST t1 where trunc (t1) a2) & lt;=to_date (' 2019-01-01 ', 'yyyy/mm/dd) and t1. The a3 & gt;=to_date (' 2019-03-01 ', 'yyyy/mm/dd)
You mean the SQL query is empty, so what do you think is wrong.
Certainly not ah, my SQL logic, judge whether 2 piece of closed interval overlap:
The beginning of the old time & lt;=new end time and the end time of the old & gt;=new start time.
But your SQL logic is:
The beginning of the old time & lt;=new beginning and the end of the old time & gt;=new over time;

CodePudding user response:

According to your meaning, it is divided into three or relationship,
New_start between old_start and old_end or
New_end between old_start and old_end or
(new_start & lt; Old_start and new_end & gt; Old_end)

Add to line directly?