I have two tables test.object and test.lock
In test.object I have constant list of objects
In test.lock I have info about locking that objects on choosen date
When I wonna lock all objects on selected date I make this "merge".
In table test.lock exists records with dates before 2014-04-15 and one on 2014-05-30
When I make this merge with date after 2014-05-02 it work ok
When I make this merge with date between 2014-04-30 and 2014-05-01 merge inserting only for small part of objects
When I make this merge with earlier date than 2014-04-30 it inserting nothing
merge into test.lock zb
using (
select unique jr_id, to_date('2014-04-29','yyyy-mm-dd') lock_date
from test.obiect where status='A'
) jr on (
zb.jr_id = jr.jr_id and zb.lock_date = jr.lock_date
)
WHEN NOT MATCHED THEN
INSERT (id,jr_id,lock_date)
VALUES (test.test_id.nextval,jr.jr_id,jr.lock_date);
If I doing somthing wrong ??