SQL Server – Alternative to Updating Every Row in a Table

sql serversql-server-2008-r2t-sqlupdate

My syntax below works (may not be the most efficient syntax, but it almost serves the purpose). The issue that I have with it, is that when the UPDATE statement is run, due to the JOIN condition each row that fits the criteria is updated. I would like to only UPDATE 1 row, per empName. Maybe I could use the field AcctIDWorked to update either the MAX() or the MIN() of this field? Doesn't matter either way honestly as I just need ONE row per empName updated.

How can I accomplish this?

DDL Below and current syntax

IF OBJECT_ID('dbo.__SalesDetailInfo', 'U') IS NOT NULL 
    DROP TABLE dbo.__SalesDetailInfo; 

IF OBJECT_ID('dbo.__Sales', 'U') IS NOT NULL 
    DROP TABLE dbo.__Sales; 

Create Table __Sales
(
    EmpName varchar(max)
    ,TotalSaleAmount decimal(10,2)
)

Insert Into __Sales (EmpName, TotalSaleAmount) Values
('OWT', '167.30'),('Fit D', '512.22'),('Mike',  '4147.60')
,('Curtis', '66.96'),('Curtis', '1165.58'),('Curtis',   '1507.64')
,('Curtis', '1551.79'),('Curtis',   '8132.13'),('Curtis',   '28.62')
,('Sam',    '499.00'),('Curtis',    '1159.76'),('Sam',  '2011.98')
,('Mike',   '450.00'),('Mike',  '1453.60'),('Sam',  '762.45')
,('Curtis', '1056.00'),('Mike', '1596.50'),('OWT',  '1768.80')
,('Mike',   '1200.00'),('Mike', '1500.00'),('Fit D',    '17921.30')
,('Curtis', '456.73'),('Curtis',    '944.23'),('Curtis',    '2242.00')
,('Mike',   '437.50'),('Sam',   '3258.07'),('Sam',  '3258.07')
,('OWT',    '7263.57'),('OWT',  '7225.57'),('OWT',  '224.63')
,('Curtis', '569.89')

Create Table __SalesDetailInfo
(
    EmpName varchar(max)
    ,TotalSales decimal(10,2)
    ,AcctIDWorked int
)

Insert Into __SalesDetailInfo (EmpName, AcctIDWorked) Values
('Curtis', 1), ('Curtis', 2), ('Curtis', 3), ('Curtis', 4)
,('Fit D', 22), ('Fit D', 11), ('Fit D', 44)
,('Mike', 110), ('Mike', 111), ('Mike', 113), ('Mike', 114)
,('Sam', 300), ('Sam', 310), ('Sam', 340)
,('OWT', 500), ('OWT', 550), ('OWT', 580), ('OWT', 590)

;WITH UPDATECTE AS
(Select EmpName, sumTSA=SUM(TotalSaleAmount)
FROM __Sales
GROUP BY EmpName)
UPDATE __SalesDetailInfo
SET TotalSales = sumTSA
FROM UPDATECTE cte
  INNER JOIN __SalesDetailInfo t
    ON RTRIM(LTRIM(cte.EmpName)) = RTRIM(LTRIM(t.EmpName))

Best Answer

select t.*, s.sumTSA
from __SalesDetailInfo t
join ( Select EmpName, sumTSA = SUM(TotalSaleAmount)
       FROM __Sales
       GROUP BY EmpName
     ) as s
ON RTRIM(LTRIM(s.EmpName)) = RTRIM(LTRIM(t.EmpName))

You could use that to create a view.