这是在Oracle数据库中使用MERGE的唯一方法吗？
这是在Oracle数据库中使用MERGE的唯一方法吗？
提问于 2015-11-06 09:49:53
我正在移动Server数据库项目的第一步，我正在寻找一种方法，可以在部署后脚本中填充引用表，而无需每次删除和填充它们。经过一些研究，我无意中发现了这篇博文：
http://blogs.msdn.com/b/ssdt/archive/2012/02/02/including-data-in-an-sql-server-database-project.aspx
该示例基本上描述了合并语句的行为：
-- Reference Data for AddressType
MERGE INTO AddressType AS Target
USING (VALUES
(0, N'Undefined'),
(1, N'Billing'),
(2, N'Home'),
(3, N'Main Office'),
(4, N'Primary'),
(5, N'Shipping'),
(6, N'Archive')
)
AS Source (AddressTypeID, Name)
ON Target.AddressTypeID = Source.AddressTypeID
-- update matched rows
WHEN MATCHED THEN
UPDATE SET Name = Source.Name
-- insert new rows
WHEN NOT MATCHED BY TARGET THEN
INSERT (AddressTypeID, Name)
VALUES (AddressTypeID, Name)
-- delete rows that are in the target but not the source
WHEN NOT MATCHED BY SOURCE THEN
DELETE由于我也使用Oracle数据库，所以我尝试将前面的示例修改为Oracle 10g语法，并生成了如下所示：
-- Reference Data for AddressType
MERGE INTO AddressType AS T
USING (WITH CTE AS (
SELECT 0 AS AddressTypeID, 'Undefined' AS Name FROM DUAL
UNION
SELECT 1 AS AddressTypeID, 'Billing' AS Name FROM DUAL
UNION
SELECT 2 AS AddressTypeID, 'Home' AS Name FROM DUAL
UNION
SELECT 3 AS AddressTypeID, 'Main Office' AS Name FROM DUAL
UNION
SELECT 4 AS AddressTypeID, 'Primary' AS Name FROM DUAL
UNION
SELECT 5 AS AddressTypeID, 'Shipping' AS Name FROM DUAL
UNION
SELECT 6 AS AddressTypeID, 'Archive'AS Name FROM DUAL
)
SELECT AddressTypeID, Name FROM CTE
)
AS S
ON T.AddressTypeID = S.AddressTypeID
-- update matched rows
WHEN MATCHED THEN
UPDATE SET T.Name = S.Name
-- insert new rows
WHEN NOT MATCHED
INSERT (T.AddressTypeID, T.Name)
VALUES (S.AddressTypeID, S.Name)除了不是完全等价的(对于没有源代码的行没有删除)，我发现语法，特别是ankward。
对于Oracle SQL是否有可用的语法快捷方式？
回答 1
Stack Overflow用户
回答已采纳
发布于 2015-11-06 09:58:55
我看不到任何快捷方式，MERGE的语法就是您所拥有的。
唯一可以去掉的是WITH子句，即中的CTE使用子句。你可以简单地把它说成：
-- Reference Data for AddressType
MERGE INTO AddressType AS T
USING (
SELECT 0 AS AddressTypeID, 'Undefined' AS Name FROM DUAL
UNION
SELECT 1 AS AddressTypeID, 'Billing' AS Name FROM DUAL
UNION
SELECT 2 AS AddressTypeID, 'Home' AS Name FROM DUAL
UNION
SELECT 3 AS AddressTypeID, 'Main Office' AS Name FROM DUAL
UNION
SELECT 4 AS AddressTypeID, 'Primary' AS Name FROM DUAL
UNION
SELECT 5 AS AddressTypeID, 'Shipping' AS Name FROM DUAL
UNION
SELECT 6 AS AddressTypeID, 'Archive'AS NAME FROM DUAL
) AS S
ON T.AddressTypeID = S.AddressTypeID
-- update matched rows
WHEN MATCHED THEN
UPDATE SET T.Name = S.Name
-- insert new rows
WHEN NOT MATCHED
INSERT (T.AddressTypeID, T.Name)
VALUES (S.AddressTypeID, S.Name)当然，您不能更新ON子句中提到的列。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/33563869
复制相关文章
相似问题
腾讯云开发者
