Translate

Thursday, July 24, 2025

TSQL - Set A Variable While Updating

I needed to set the value in a variable and update a field at the same time This is the update query.

DECLARE @numberOfCountersNeeded as int;
DECLARE @newcounterval as bigint;
SET @numberOfCountersNeeded = 3
UPDATE c --get the counter interval and update the current count
SET @newcounterval = somefield = somefield + @numberOfCountersNeeded
FROM sometable c
WHERE conditions

Friday, July 4, 2025

TSQL - Delete Duplicate Rows

This was the solution to a problem that was posted on Reddit. The OP asked how to delete a duplicate row. In the question, they just needed to delete a single duplicate. But then I wondered about cases where there was more than a single duplicate. In the example, EmployeeID is the primary key.

This is the finale SQL:
DECLARE @nC int; --number of records to delete
DECLARE @id int = 5; --key

SELECT @nc = count(*)-1 from [dbo].[TestTable]
WHERE EmployeeID = @id

DELETE top (@nc) from [dbo].[TestTable]
WHERE EmployeeID = @id