Oracle MERGE:仅触发NOT MATCHED [英] Oracle MERGE: only NOT MATCHED is triggered

问题描述

数据库: Oracle

表格:

CREATE TABLE TABLE_FOR_TESTS (
    d DATE,
    t NUMBER(8)
)

合并:

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)

或

    ...
    ON ((SELECT T FROM TABLE_FOR_TESTS) is not null)
    ...

我将参考MERGE的第一个版本，但是第二个版本具有相同的效果.

I will refer to the first version of MERGE, but the second one has the same effect.

1)我第一次运行MERGE

1) I run that MERGE for the first time

• 结果:预期(因为没有任何元素，因此ON条件为false => INSERT )

• result: expected (because there is no element, the ON condition is false => INSERT)

2)我在这里运行:

SELECT COUNT(*) FROM TABLE_FOR_TESTS

其输出为"1".

3)我第二次运行该合并

3) I run that MERGE for the second time

• 结果:意外( INSERT )，预期:UPDATE(仅适用于sqlfiddle)

• result: unexpected (INSERT), expected: UPDATE (it works only on sqlfiddle)

为什么在第N次运行(N> 1)中 ON 条件为假? (如果在输出2时为"1"))

Why is ON condition false at the N-th run (N>1) ? ( if it was "1" as output at 2) )

(只是测试:如果我在第二次运行之前将条件更改为ON (1=1)，则效果很好:更新已完成)

(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)

联接在表的主键上，并根据该PK值的记录是否存在而更新或插入.

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.

每天最多有一条记录，并且t将保存每天该语句的执行次数(假设TABLE_FOR_TESTS上没有其他DML).

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).

注意:sysdate本身包含一个时间成分. trunc(sysdate)会将其删除并将时间设置为00:00:00.

Note: sysdate by itself includes a time component. trunc(sysdate) removes it and sets the time to 00:00:00.

这篇关于Oracle MERGE:仅触发NOT MATCHED的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！