SQL Server : add row if doesn't exist, increment value of one column, atomic

Question

I have a table that keeps a count of user actions. Each time an action is done, the value needs to increase. Since the user can have multiple sessions at the same time, the process needs to be atomic to avoid multi-user issues. The table has 3 columns: - `ActionCode` as `varchar` - `UserID` as `int` - `Count` as `int` I want to pass `ActionCode` and `UserID` to a function that will add a new row if one doesn't already exist, and set count to 1. If the row does exist, it will just increase the count by one. `ActionCode` and `UserID` make up the primary unique index for this table. If all I needed to do was update, I could do something simple like this (because an `UPDATE` query is atomic already): ``` UPDATE (Table) SET Count = Count + 1 WHERE ActionCode = @ActionCode AND UserID = @UserID ``` I'm new to atomic transactions in SQL. This question has probably been answered in multiple parts here, but I'm having trouble finding those and also placing those parts in one solution. This need…

Accepted Answer

Assuming you are on SQL Server, to make a single atomic statement you could use MERGE

```
MERGE YourTable AS target
USING (SELECT @ActionCode, @UserID) AS source (ActionCode, UserID)
ON (target.ActionCode = source.ActionCode AND target.UserID = source.UserID)
WHEN MATCHED THEN 
    UPDATE SET [Count] = target.[Count] + 1
WHEN NOT MATCHED THEN   
    INSERT (ActionCode, UserID, [Count])
    VALUES (source.ActionCode, source.UserID, 1)
OUTPUT INSERTED.* INTO #MyTempTable;
```

UPDATE Use output to select the values if necessary. The code updated.

SQL Server : add row if doesn't exist, increment value of one column, atomic

Solution

Problem

Related problems