Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

http://sqlfiddle.com/#!3/96f11/3

In the above fiddle, I need the required output in the Fiddle.

ie., The UserID column in Filtered Table and Main table are equal. I need to get the Amt column data according to the Filtered table from Main table and Add them and add the Total amount to The Amt Column of UserID Admin Row. and the Corresponding Amt Rows have to be set to Zero.

I need to update the output into Main table

Can you help me in doing This ??

share|improve this question

closed as too localized by Mark Storey-Smith, Shawn Melton, Mat, Max Vernon, StanleyJohns Apr 29 '13 at 11:52

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

3 Answers 3

up vote 4 down vote accepted
WITH Total AS
(
  SELECT 
    SUM(m.AMT) AS AMT
  FROM dbo.Main m
  INNER JOIN dbo.Filtered f
    ON m.UserID = f.UserID
)

UPDATE mn
SET AMT = CASE mn.UserID
            WHEN 'Admin' THEN (mn.AMT + t.AMT)
            ELSE 0
          END
FROM dbo.Main mn
CROSS APPLY Total t
WHERE EXISTS (SELECT fl.UserID 
              FROM dbo.Filtered fl
              WHERE fl.UserID = mn.UserID) 
      OR mn.UserID = 'Admin'; 

Here is the sqlfiddle

Update,

Use Paul White's approach, much more efficient.

share|improve this answer

Another approach (SQLfiddle):

WITH ToUpdate AS
(
    SELECT
        m.Amt, 
        NewAmt = 
            CASE 
                WHEN m.UserID = 'Admin' 
                THEN SUM(m.Amt) OVER ()
                ELSE 0 
            END
    FROM dbo.Main AS m
    WHERE m.UserID = 'Admin' 
    OR EXISTS (SELECT 1 FROM dbo.Filtered AS f WHERE m.UserID = f.UserID)
)
UPDATE ToUpdate
SET amt = NewAmt;
share|improve this answer
    
I was trying to get pass the sum aggregate within the update, but forgot that it could be done within CTE and avoid reading from the tables twice. Nicely done. –  Travis Gan Apr 28 '13 at 22:43
    
Thanks @TravisGan - there's nothing special about a CTE though, one could equally well use a derived table. Also, this is just an alternative - the SUM OVER results in a segment spool which may or may not be as efficient as your original solution :) –  Paul White Apr 29 '13 at 6:41

Below is how you can achieve

Required results --

select *
from [Output]
order by UserID

Required Output

Query to achieve Required results --

select UserID
    ,Amt = case 
        when UserID in (
                select UserID
                from Filtered
                )
            then '0.00'
        when UserID = 'Admin'
            then (
                    select sum(M.Amt)
                    from dbo.Main M
                    inner join dbo.Filtered F on M.UserID = F.UserID
                    ) + sum(Amt)
        else Amt
        end
from Main
group by UserID
    ,Amt
order by UserID

Expected Output

share|improve this answer
    
Thank you But I need to Update the Main Table as the output table. I'm so sorry that I missed to mention that in the question –  Krishna Thota Apr 28 '13 at 1:55

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