Update a table from a sum with a condition from another table

Question

_{Wednesday, July 10, 2013 7:57 PM}

I can't find a way to Update a table with a sum from another table

My sum statement by itself works

SELECT SUM (Amount2Deduct) as s1 FROM PTD WHERE  PreTax = 1

What do I need to make this combination work?

UPDATE PT

SET Gross - (SELECT SUM (Amount2Deduct) as s1

FROM PTD WHERE PT.EmployeeID = PTD.EMployeeID

AND PreTax = 1

Help appreciated

All replies (3)

_{Thursday, July 11, 2013 2:34 AM ✅Answered}

You can try

SELECT PT COUMNS,

(SELECT  SUM (Amount2Deduct)  FROM PTD WHERE PT.EmployeeID = PTD.EMployeeID and PT.Pretax=PTD.Pretax)

GROUP BY PreTax ) AS PTDSUM

FROM PT

For more accurate query please post your PT and PTD table stucture.

Gaurav Gupta http://sqlservermsbiblog.blogspot.com/ Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

_{Thursday, July 11, 2013 6:59 AM ✅Answered}

UPDATE PT

SET Gross= (SELECT SUM (Amount2Deduct) as s1

FROM PTD WHERE PT.EmployeeID = PTD.EMployeeID

AND PreTax = 1) where exists 

(

SELECT *

FROM PTD WHERE PT.EmployeeID = PTD.EMployeeID

AND PreTax = 1

)

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

_{Thursday, July 11, 2013 2:18 AM | 1 vote}

You can try like below

declare @sum int

select @sum =
SUM (Amount2Deduct) FROM PTD WHERE  PreTax = 1

Update PT SET Gross=@sum

Thanks & Regards RAJUKIRAN L Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.