在同一SQL语句中更新和/或插入数行

Question

我正在实现一个帐户“能力”类型的东西-一个用户帐户被赋予在系统中做一件或多件事情的能力，例如(简单的例子)取消订单或支付发票。我有一张这样的桌子：

ac_id     ability        active
  1        CANCELORDER      Y
  1        PAYINVOICE       Y
  1        EDITOREDR        Y
  2        PAYINVOICE       Y
  2        PAYWAGES         Y
  2        CANCELORDER      Y

ac_id是到帐户表的链接。

当首次使用某些功能创建用户帐户时，很容易使用insert语句将这些功能插入到表中。

当用户帐户的能力列表被更改时，更新似乎很棘手。以下是我认为需要做的事情：

list      DB
ABC       ABC
GHI       DEF
JKL       JKL
MNO       MNO

如果list包含帐户的新能力列表，DB包含该帐户的当前列表，则需要

1. 从DB列表中删除DEF，因为它不在新列表中。
2. 将GHI添加到数据库列表中。

第一个是简单明了的SQL，如：

update userabilitytest set
  active = 'N'
where
  account_id = 1 and 
  active = 'Y' and
  ability not in (''ABC', 'GHI', 'JKL', 'MNO' ) 

(它将活动标志设置为'N‘，而不是实际删除)。

然而，我想不出一种做第二部分的方法。我通过在我的应用程序代码中找出这个用户的数据库中是否已经有了这种能力来工作，这就为每个还没有在数据库中的能力做了插入。

最好将其插入SQL中并在服务器上运行。

有什么想法吗？

Answer 1

在@Phil指出了正确的方向之后，下面的合并看起来会起作用：

MERGE userabilitytest
USING (VALUES 
        (1, 'EDITACCOUNT'),
        (1, 'EDITORDER'),
        (1, 'BOOHOO'),
        (1, 'FooBar') 
        )
      AS foo(account_id, ability)
ON 
  userabilitytest.account_id = foo.account_id and 
  userabilitytest.ability = foo.ability and
  userabilitytest.active = 'Y'
WHEN MATCHED THEN
  UPDATE SET ability = foo.ability
WHEN NOT MATCHED THEN
  INSERT (account_id, ability) 
  VALUES (account_id, ability);

能够使用“值”列表并将其归给"foo“，这一切都是不同的--一个包含新值的表实际上是不必要的。

另外，还需要某种语句，其中需要更新，但当前的语句没有任何功能，因为ON部分中的能力是匹配的。注释UPDATE语句out会导致错误。