Sql-server – Updating and/or inserting several rows in the same SQL statement

sql serversql-server-2008

I am implementing an account "ability" type thing – a users account is given the abilty to do one or many things in the system, like (simple examples) cancel orders or pay invoices. I have a table like this:

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

ac_id is a link to the account table.

When a user account is first created with some abilities it is easy to insert the abilities into the table with an insert statement.

When a user account's ability list is changed updating seems to be tricky. Here's what I think needs to be done:

list      DB
ABC       ABC
GHI       DEF
JKL       JKL
MNO       MNO

If list contains the new list of abilities for an account and DB contains the current list for that account we need to

  1. remove DEF from the DB list because it is not in the new list.
  2. add GHI into the DB list.

The first is straightforward SQL like:

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

(which sets the active flag to 'N' rather than actually deleting).

However, I can't think of a way of doing the 2nd part. I have it working by figuring out whether the ability is already in the DB for that user in my application code, which does insert for each not-yet-in-the-db ability.

It would be nice to push this into SQL and run on the server.

Any ideas?

Best Answer

Having been pointed in the right direction by @Phil, it looks like the following merge will work:

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);

Being able to use a "values" list and ascribing it to "foo" makes all the difference - a table with the new values in is not actually necessary.

Also, some sort of statement where UPDATE is is needed, but the one that is there at the moment does nothing as ability is matched in the ON part. Commenting the UPDATE statement out causes an error.

Related Question