CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-update
EthernetCable
EthernetCable

Reputation: 1327

SQL Server Incrementing a counter in Update

I'm updating some columns and incrementing a counter whenever I change a row.

The update statement is the result of a join (simplified code below):

update @to
    set
        t.num += 1
    from @to t
    join @source s
        on t.id = s.id

When I update one row more than once, the columns hold the last value (as they should), but the counter is only incremented once. So if the join returns (id = 1, id = 1), my table holds (id = 1, num = 1) rather than (id = 1, num = 2).

There are ways to get around this (another join on a select count, for example), but I wonder if there's a way to keep it simple.

Upvotes: 2

Views: 3114

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

There's not really a way to get the count without getting the count. Here is one way to do that (and still only referencing @source once):

;WITH s AS
(
  SELECT id, c = COUNT(*)
    FROM @source 
    GROUP BY id
)
UPDATE t SET t.num += s.c
  FROM @to AS t
  INNER JOIN s
  ON t.id = s.id;

Hopefully the rows that end up in @source are already filtered down to only those that will also be found in @to. If not, you can add more conditions to the initial CTE.

Upvotes: 2

Related Questions