oracle 合并，匹配时插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11924657/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Merge, when matched insert
提问by z22
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.
我如何在 Oracle 中使用合并来做到这一点？下面是我的代码，它给出了“缺少关键字”错误。
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);
Also, I dont know how to add current timestamp along with the studName. Any suggestions for the same would be welcome.
另外，我不知道如何将当前时间戳与studName. 任何相同的建议将受到欢迎。
采纳答案by Alessandro Rossi
This shouldn't be done with a merge (as Justin said) but with two inserts. Just one rowfor 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!!!
并且不要颠倒这个执行顺序！！！
回答by Justin Cave
If you are going to insert a new row whether or not there is a match, it wouldn't make sense to use a MERGE. Simply do an INSERT.
如果您要插入一个新行，无论是否存在匹配项，使用MERGE. 只需做一个INSERT.
INSERT INTO student( studID, studName, <<timestamp column>> )
SELECT (CASE WHEN s.studId IS NOT NULL -- If there is a match
THEN <<student ID sequence>>.nextval
ELSE sl.studID
END),
studName,
(CASE WHEN s.studId IS NOT NULL -- If there is a match
THEN systimestamp
ELSE NULL
END)
FROM studLoad sl
LEFT OUTER JOIN student s ON (sl.studID = s.studId)
Note that I'm assuming that your studenttable actually has a column to store the current timestamp-- use that column name in place of <<timestamp column>>in the SQL statement above.
请注意，我假设您的student表实际上有一个列来存储当前时间戳——使用该列名代替<<timestamp column>>上面的 SQL 语句。
