Database: Oracle
Table:
CREATE TABLE TABLE_FOR_TESTS (
d DATE,
t NUMBER(8)
)
MERGE:
MERGE INTO TABLE_FOR_TESTS
USING DUAL
ON ((SELECT COUNT(*) FROM TABLE_FOR_TESTS) = 1)
WHEN MATCHED THEN
UPDATE SET T = T+1
WHEN NOT MATCHED THEN
INSERT (D, T) VALUES (sysdate, 1)
or
...
ON ((SELECT T FROM TABLE_FOR_TESTS) is not null)
...
I will refer to the first version of MERGE, but the second one has the same effect.
1) I run that MERGE for the first time
2) Here I run:
SELECT COUNT(*) FROM TABLE_FOR_TESTS
and it's output is "1".
3) I run that MERGE for the second time
Why is ON condition false at the N-th run (N>1) ? ( if it was "1" as output at 2) )
(just to test: if I change the condition to be ON (1=1)
before the second run, it works well: UPDATE is done)
I think you have misunderstood what merge is for.
I would expect your table to be something like:
CREATE TABLE TABLE_FOR_TESTS (
d DATE,
t NUMBER(8),
CONSTRAINT TABLE_FOR_TESTS_PK PRIMARY KEY (d)
)
and then the merge statement could be:
MERGE INTO TABLE_FOR_TESTS t
USING (SELECT trunc(sysdate) d FROM DUAL) s
ON (s.d = t.d)
WHEN MATCHED THEN
UPDATE SET t = t+1
WHEN NOT MATCHED THEN
INSERT (d, t) VALUES (trunc(sysdate), 1)
where the join is on the primary key of the table and either update or insert depending on whether the record for that PK value exists.
This would have a maximum of one record per day and t would hold the number of executions of this statement per day (assuming no other DML on TABLE_FOR_TESTS).
Note: sysdate by itself includes a time component. trunc(sysdate) removes it and sets the time to 00:00:00.