0

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))
  • 1
    Why are you storing redundant aggregate information in any row in this table? – Aaron Bertrand Feb 6 '17 at 3:01
  • @AaronBertrand - I am trying to uncover how to update only 1 row - which would alleviate the storing redundant aggregate info, correct? – StarsFlyFree FromCozyNights Feb 6 '17 at 3:03
  • 2
    By redundant I mean you can always calculate the SUM when you need it. Why store information that you have to keep updated constantly in order to be accurate, every time any row is updated? – Aaron Bertrand Feb 6 '17 at 3:08
  • Very valid point. Would the query be more efficient like this (I have not tested this syntax, but should get the point across as to what I was thinking of setting up) ---- Select sdi.empName ,TotalSalesAmount = COALESCE((Select SUM(s,TotalSalesAmount) FROM __Sales s where Where s.empName = sdi.empName),0) FROM __SalesDetailInfo sdi – StarsFlyFree FromCozyNights Feb 6 '17 at 3:14
0
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.