使用子句
|中的SQL Server MERGE语句
使用Oracle(或DB2,HSQLDB),我可以表达以下内容:
MERGE INTO [target_table]
USING (SELECT 1 FROM dual)
ON [target_table.id = 5]
WHEN MATCHED THEN UPDATE ...
WHEN NOT MATCHED THEN INSERT ...
这只是检查target_table
中是否已经存在带有id = 5
的记录。如果存在,则记录将更新,如果不存在,则将其插入。这与MySQL更简洁的大致相同
INSERT INTO [target_table] ...
ON DUPLICATE KEY UPDATE ...
如何在SQL Server中做到这一点?根据文档,“ 4”必须是以下任意一项:
<table_source> ::=
{
table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ]
[ WITH ( table_hint [ [ , ]...n ] ) ]
| rowset_function [ [ AS ] table_alias ]
[ ( bulk_column_alias [ ,...n ] ) ]
| user_defined_function [ [ AS ] table_alias ]
| OPENXML <openxml_clause>
| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
| <joined_table>
| <pivoted_table>
| <unpivoted_table>
}
http://msdn.microsoft.com/de-de/library/bb510625.aspx
显然,SQL Server没有DUAL
表,但也不允许SELECT
语句。我可以把“ 4”当成什么?
N.B:我发现可以创建一个虚拟视图
CREATE VIEW dummy (one) AS SELECT 1;
并提供为ѭ4supply但是我想省略DDL语句只是为了能够执行此MERGE
语句
没有找到相关结果
已邀请:
1 个回复
蹦吃舷弦
(又称为表值构造函数):
快乐的编码。