Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 (中文) » SQL Anywhere 服务器 - SQL 参考 » 使用 SQL » SQL 语句 » SQL 语句 (E-O)

 

MERGE 语句

此语句用于将多个表、视图和系统过程结果合并到一个表或视图中。

语法
MERGE
INTO target-object [ into-column-list ]
USING [ WITH AUTO NAME ] source-object
   ON merge-search-condition
merge-operation […]
 [ OPTION ( query-hint, ... ) ]
target-object: 
[ userid.]target-table-name [ [ AS ] target-correlation-name ]  
| [ userid.]target-view-name [ [ AS ] target-correlation-name ] 
| ( select-statement ) [ AS ] target-correlation-name 
source-object : 
[ userid.]source-table-name [ [ AS ] source-correlation-name ] [ WITH ( table-hints ) ]
| [ userid.]source-view-name [ [ AS ] source-correlation-name ] 
| [ userid.]source-mat-view-name [ [ AS ] source-correlation-name ] 
| ( select-statement ) [ AS ] source-correlation-name [  using-column-list  ]
| procedure
procedure :
[ owner.]procedure-name ( procedure-syntax )
   [ WITH ( column-name data-type, ... ) ]
   [ [ AS ] source-correlation-name ]
merge-search-condition :
search-condition
| PRIMARY KEY
merge-operation :
WHEN MATCHED [ AND search-condition ] THEN match-action
| WHEN NOT MATCHED [ AND search-condition ] THEN not-match-action
match-action :
DELETE
| RAISERROR [ error-number ]
| SKIP 
| UPDATE SET set-item, ...  
| UPDATE [ DEFAULTS { ON | OFF } ]
not-match-action :
INSERT 
| INSERT [ insert-column-list ] VALUES ( value, ... ) 
| RAISERROR [ error-number ]
| SKIP
set-item :
[target-correlation-name.]column-name = { expression | DEFAULT }
| [ owner-name.]target-table-name.column-name = { expression | DEFAULT }
insert-column-list : 
( column-name, ... )
query-hint :
MATERIALIZED VIEW OPTIMIZATION option-value
| FORCE OPTIMIZATION
| option-name = option-value
into-column-list :
( column-name, ... )
using-column-list :
( column-name, ... )
error-number : positive integer or variable greater than 17000
option-name : identifier
option-value : hostvar (indicator allowed), string, identifier, or number

table-hints:请参见FROM 子句

search-condition:请参见搜索条件

set-clause-list:请参见SET 语句

参数
  • INTO 子句   此子句用于为 MERGE 语句定义目标对象。target-object 可以是基表、常规视图或派生表的名称,但不可以是实例化视图。派生表或视图必须代表可更新的查询块。例如,如果视图或派生表的定义中包含了 UNION、INTERSECT、EXCEPT 或 GROUP BY,那么它不可用作 MERGE 语句的目标对象。

    target-object 为派生表时,可选的 into-column-list 能够用于为派生表的列提供替代名称。以这种方式使用时,into-column-list 的大小必须与派生表的列列表匹配,并且两个列表的顺序必须相同。

    target-object 为基表或视图时,into-column-list 可用于将表或视图列的子集指定为与 MERGE 语句的其余部分相关。

    数据库服务器使用 into-column-list 来解决:

    • WHEN MATCHED 子句中不带 SET 子句的 UPDATE

    • WHEN NOT MATCHED 子句中不带 VALUES 子句的 INSERT

    • ON 子句中的 PRIMARY KEY 搜索条件

    • USING 子句中的 WITH AUTO NAME 子句

    如果没有指定 into-column-list,则假定 into-column-list 包含 target-object 的所有列。

  • USING 子句   此子句用于定义要合并的数据的源。source-object 可以是基表(包括表提示)、视图、实例化视图、派生表或过程。如果 source-object 是派生表,则可以指定 using-column-list。如果不指定 using-column-list,则使用 source-object 的所有列。

  • WITH AUTO NAME 子句   此子句用于指定服务器在用于合并操作的 target-object 中自动使用列名称来匹配 into-column-list 中的列。以下两个示例是等效的,它们演示了当指定 WITH AUTO Name 时,into-column-list 中列的顺序如何更改以便与 source-object 中的列名相匹配:
    ... INTO T ( Name, ID, Description ) 
       USING WITH AUTO NAME ( SELECT Description, Name, ID FROM PRODUCTS WHERE Description LIKE '%cap%') 
    ... INTO T ( Description, Name, ID ) 
       USING ( SELECT Description, Name, ID FROM PRODUCTS WHERE Description LIKE '%cap%' )

  • ON 子句   此子句用于指定 source-object 中的行与 target-object 中的行相匹配的条件。

    有关搜索条件语法的详细信息,请参见搜索条件

    可以指定 ON PRIMARY KEY 以便根据 target-object 的主键定义来匹配 source-object 的行。source-object 不需要主键。然而,target-object 必须具有主键。当指定 ON PRIMARY KEY 时:

    • 如果 target-object 不是基表,或它没有主键,则返回错误。

    • 如果有一个或多个主键列没有包含在 into-column-list 中,则返回错误。

    • 只要 into-column-list 中的所有主键列在 using-column-list 中都有相应的匹配列,into-column-listusing-column-list 中的列数可以不同。例如,如果 into-column-list 为 (I1, I2, I3),using-column-list 为 (U1, U2),同时主键列为 (I2, I3),则由于 target-object 主键的 (I3) 列在 using-column-list 中没有与其匹配的列,因此返回错误。

    • 无论主键的定义为何,into-column-list 中的主键列与 using-column-list 中的表达式都要根据 into-column-list 中主键列的位置来相互匹配。例如,假设将 target-object 上的主键定义为 (B, C),而 into-column-list 为 (E, C, F, A, D, B)。指定了 ON PRIMARY KEY 时,将 target-object 的 B 列与 using-column-list 的第六个元素进行比较,因为 B 列在 into-column-list 的第六个位置。同样,target-object 的 C 列与 using-column-list 的第二个元素进行比较。

      ON PRIMARY KEY 是相应的 ON 条件的语法速记。例如,假定 into-column-list 为 (I1, I2, ..In),而相应匹配的 using-column-list 为 (U1, U2, ..Um)。同时假定 target-object 的主键列为 I1、I2、I3,并且所有的主键列都包含在 into-column-list 中。在这种情况下,merge-search-condition 将被定义为合取式 "I1=U1 AND I2=U2 AND I3=U3"

  • WHEN MATCHED 和 WHEN NOT MATCHED 子句   WHEN MATCHED 和 WHEN NOT MATCHED 子句用于定义 source-object 中的行与 target-object 中的行匹配或不匹配时分别采取的操作。可在 THEN 关键字后指定操作。可通过指定附加的 AND 子句来控制对匹配或不匹配行的子集采取的操作。

    ON 子句确定如何将 source-object 中的行分为匹配的行和不匹配的行。当 ON 子句对 target-object 中的至少一个行返回 TRUE 时,则认为 source-object 中行是匹配行。当 ON 子句对 target-object 中的任何行都不返回 TRUE 时,则认为 source-object 中的行是不匹配行。可使用多个 WHEN MATCHED 和 WHEN NOT MATCHED 子句将匹配行和非匹配行的集合划分成若干无交集子集。每个子集都由 WHEN 子句处理。按 WHEN MATCHED 和 WHEN NOT MATCHED 在 MERGE 语句中出现的顺序来处理这两个子句。

    在 WHEN MATCHED 或 WHEN NOT MATCHED 子句的 AND 子句中指定的搜索条件确定是否由指定子句处理候选行。如果指定了不带 AND 子句的 WHEN MATCHED 或 WHEN NOT MATCHED 子句,则假定 AND 子句中的搜索条件为 TRUE。如果某一行满足多个子句的 AND 条件,则该行将由 MERGE 语句中首次出现的子句处理。

    当 WHEN MATCHED 子句中的任何一个子句多次处理 target-object 的同一个行时,将返回错误。如果 target-object 的行与 source-object 的两个不同的行匹配,那么这个行可多次属于同一个 WHEN MATCHED 子句的同一子集。

    在下面的示例中,因为 target-object Products 中 ID 为 300 的行与 source-object SalesOrderItems 的 111 个行匹配,所以返回错误。所有的匹配行都属于相应 WHEN MATCHED THEN UPDATE 子句的同一个子集。

    MERGE INTO Products 
       USING SalesOrderItems S
       ON S.ProductID = Products.ID
       WHEN MATCHED THEN UPDATE SET Products.Quantity = 20;

    WHEN MATCHED:对于匹配行,可以指定以下 match-action 操作之一:

    • DELETE   指定 DELETE 将删除 target-object 中的行。

    • RAISERROR   指定 RAISERROR 将终止合并操作,回退任何更改并返回错误。缺省情况下,当指定了 RAISERROR 时,数据库服务器返回 SQLSTATE 23510 和 SQLCODE -1254。也可以通过在 RAISERROR 关键字后指定 error-number 参数来自定义返回的 SQLCODE。自定义 SQLCODE 必须是大于 17000 的正整数,并且可以指定为数字或变量。指定自定义 SQLCODE 时,返回的数值为负数。

      例如,如果指定 WHEN MATCHED AND search-condition THEN RAISERROR 17001,则找到符合 WHEN 子句条件的行时,合并操作失败,更改被回退,而返回的错误为 SQLSTATE 23510 和 SQLCODE -17001。请参见使用 RAISERROR 操作

    • SKIP   指定 SKIP 可跳过某个行,不对其进行任何操作。

    • UPDATE   指定 UPDATE SET 可使用 set-item 值更新行。set-item 为简单赋值表达式,其中将列设置为 expression 的值。对表达式没有任何限制。也可以指定 DEFAULT,将列设置成已为其定义的缺省值。

      例如,UPDATE SET target-column1=DEFAULT, target-column2=source-column2 将 target-column1 设置为它的缺省值,将 target-column2 的值设置为与 source-object 中 source-column2 的修改行相同。

      如果没有指定 SET 子句,SET 子句会通过 into-column-listusing-column-list 定义。例如,如果 into-column-list 为 (I1, I2, .. In),using-column-list 为 (U1, U2, .. Un),则假定 SET 子句为 "SET I1=U1 , I2=U2 , .. In=Un"

    WHEN NOT MATCHED:对于不匹配行,可以指定以下 match-action 操作之一:

    • INSERT   指定 INSERT ...VALUES 可使用指定的值插入行。如果指定不带 VALUES 子句的 INSERT 子句,VALUES 子句会通过 into-column-listusing-column-list 定义。例如,如果 into-column-list 为 (I1, I2, .. In),using-column-list 为 (U1, U2, .. Un),则不包含 VALUES 子句的 INSERT 相当于 INSERT (I1, I2, .. In) VALUES (U1, U2, .. Un)

    • RAISERROR   指定 RAISERROR 将终止合并操作,回退任何更改并返回错误。指定 RAISERROR 时,缺省条件下数据库服务器返回 SQLSTATE 23510 和 SQLCODE -1254。另外,在 RAISERROR 关键字之后,可通过指定 error-number 参数自定义返回的 SQLCODE。自定义 SQLCODE 必须是大于 17000 的正整数,并且可以指定为数字或变量。指定自定义 SQLCODE 时,返回的数值为负数。

      例如,如果指定 WHEN NOT MATCHED AND search-condition THEN RAISERROR 17001,则找到符合 WHEN 子句条件的行时,合并操作失败,更改被回退,而返回的错误为 SQLSTATE 23510 和 SQLCODE -17001。请参见使用 RAISERROR 操作

    • SKIP   指定 SKIP 可跳过某个行,不对其进行任何操作。

  • OPTION 子句   此子句用于指定执行语句时的提示。支持以下提示:

    • MATERIALIZED VIEW OPTIMIZATION option-value
    • FORCE OPTIMIZATION
    • option-name = option-value

    有关这些选项的说明,请参见SELECT 语句的 OPTIONS 子句。

注释

source-object 中的行与 target-object 中的行进行比较,并根据它们是否符合 ON 子句的条件来决定二者是匹配还是不匹配。如果 target-table 中至少存在一行使 merge-search-condition 的值为 true,则将 source-object 中的行视为匹配。随后将根据 AND 子句指定的搜索条件,并按照 WHEN MATCHED 和 WHEN NOT MATCHED 子句中为它们定义的操作将匹配行和不匹配行分组。通过匹配和不匹配操作对行进行分组的过程称作分支,每一组称为一个分支

分支完成后,数据库开始执行为分支行定义的操作。分支按其出现的顺序进行处理,该顺序与 WHEN 子句在语句中出现的顺序相一致。如果分支期间 source-object 中的多个行为 target-object 中的同一行定义了操作,则合并操作失败并返回错误。这样能够防止合并操作对 target-object 中的给定行执行多次操作。

处理分支时,在事务日志中将插入、更新和删除操作分别记录为 INSERT、UPDATE 和 DELETE 语句。

有关触发器如何影响合并操作的信息,请参见使用 MERGE 语句导入数据

权限

DBA 权限,或:

  • 如果在 MERGE 语句中指定了 INSERT、UPDATE 或 DELETE 操作,需要具有对 target-object 的 INSERT、UPDATE 或 DELETE 权限。

  • 需要具有对 MERGE 语句中引用的任何对象的 SELECT 权限。

  • 需要具有对 MERGE 语句中引用的任何过程的 EXECUTE 权限。

副作用

任何为 target-object 定义的触发器都将被触发。

另请参见
标准和兼容性
  • SQL/2003   MERGE 语句是 SQL/2003 标准的特性 F312。SQL Anywhere 中的 MERGE 语句与 SQL/2003 标准中的 MERGE 语句规范相符合,并具有附加扩展。MERGE 语句的一部分附加扩展与即将发布的 SQL/2008 标准相符合。其中包括:

    • 多个 WHEN MATCHED 和 WHEN NOT MATCHED 子句

    • 后跟 [ AND search-condition ] 的 WHEN [NOT] MATCHED 子句

    • WHEN MATCHED 子句中的 DELETE

    • WHEN [NOT] MATCHED 子句中的 RAISERROR

    • WHEN [NOT] MATCHED 子句中的 SKIP

    • OPTION 子句

    • PRIMARY KEY 子句

    • DEFAULTS 子句

    • 不带 VALUES 子句的 INSERT 子句

    • WITH AUTO NAME 子句

    • 不带 SET 子句的 UPDATE 子句

示例

下面示例将派生表的行合并到 Products 表中,实际上添加的新 T 恤衫与现有 T 恤衫有相同属性,只是颜色、大小和产品标识不同。在此示例中,如果标识号为 304 的产品在 Products 表中已存在,则不插入行:

MERGE INTO Products ( ID, Name, Description, Size, Color, Quantity, UnitPrice, Photo ) 
   USING WITH AUTO NAME ( 
      SELECT 304 AS ID, 
             'Purple' AS Color, 
             100 AS Quantity, 
             Name, 
             Description, 
             Size, 
             UnitPrice, 
             Photo 
          FROM Products WHERE Products.ID = 300 ) AS DT
   ON PRIMARY KEY
   WHEN NOT MATCHED THEN INSERT;

以下示例与前一个示例相同,只是没有使用语法速记:

MERGE INTO Products ( ID, Name, Description, Size, Color, Quantity, UnitPrice, Photo ) 
   USING (
      SELECT 304 AS ID, 
             'Purple' AS Color, 
             100 AS Quantity, 
             Name, 
             Description, 
             Size, 
             UnitPrice, 
             Photo 
          FROM Products WHERE Products.ID = 300 )
          AS DT ( ID, Name, Description, Size, Color, Quantity, UnitPrice, Photo )
   ON ( Products.ID = DT.ID )
   WHEN NOT MATCHED 
      THEN INSERT ( ID, Name, Description, Size, Color, Quantity, UnitPrice, Photo )
      VALUES ( DT.ID, DT.Name, DT.Description, DT.Size, DT.Color, DT.Quantity, DT.UnitPrice, DT.Photo );

有关 MERGE 语句的详细示例,请参见使用 MERGE 语句导入数据