匹配(插入匹配时) [英] Merge, when matched insert

问题描述

我希望在Oracle中执行构造性合并，即，如果在源表中找到匹配的记录，则应将具有当前时间戳记的新记录添加到目标表中.

I wish to perform constructive merge in Oracle i.e. if a matching record is found in the source table, a new record with current timestamp should be added to the destination table.

如何在Oracle中使用合并来做到这一点?下面是我的代码，该代码给出了缺少关键字"错误.

How do I do this using merge in Oracle? Below is my code which gives a "missing keyword" error.

merge into studLoad sl
using student s
on(s.studID=sl.studID)
when matched
then
insert(sl.studID,sl.studName)
values(s.studID||'abc',s.studName)
when not matched
then
insert(sl.studID,sl.studName)
values(s.studID,s.studName);

此外，我不知道如何将当前时间戳与studName一起添加.对此的任何建议都将受到欢迎.

Also, I dont know how to add current timestamp along with the studName. Any suggestions for the same would be welcome.

推荐答案

这不应该通过合并(如Justin所说)来完成，而要进行两次插入.每次匹配仅一行(也有重复的匹配项，如您在评论中所述)，然后是学生的所有行.可以通过以下方式完成.

This shouldn't be done with a merge (as Justin said) but with two inserts. Just one row for each match (also with duplicate matchings as you said in the comment) and then all the rows from student. This can be done in the following way.

insert into studLoad ( studID, studName, <<timestamp column>> )
select studID, studName, systimestamp
from student
where studId in (
        selct studId
        from studLoad
    )
/
insert into studLoad (studID, studName)
select studID, studName
from student
/

不要颠倒这个执行顺序！！！

And don't invert this execution order!!!

这篇关于匹配(插入匹配时)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！