Larry Brister
asked on
SQL Server Update Query Streamline
The code below pulls data from a HUGE detail table and updates a summary table
Does the code itself look "streamlined" enough?
Should I also have
Where campaign_id = @campaign_id in the sub query?
Does the code itself look "streamlined" enough?
Should I also have
Where campaign_id = @campaign_id in the sub query?
BEGIN
UPDATE main
SET main.bounce = sub.bounce ,
main.click = sub.click ,
main.deferred = sub.deferred ,
main.delivered = sub.delivered ,
main.dropped = sub.dropped ,
main.group_unsubscribe = sub.group_unsubscribe ,
main.[open] = sub.[open] ,
main.processed = sub.processed ,
main.unsubscribe = sub.unsubscribe
FROM dbo.SendGridSummary main
JOIN (
SELECT Campaign_ID ,
Customer_ID ,
email ,
MAX(CASE WHEN _event = 'bounce' THEN 1
ELSE 0
END) [bounce] ,
MAX(CASE WHEN _event = 'click' THEN 1
ELSE 0
END) [click] ,
MAX(CASE WHEN _event = 'deferred' THEN 1
ELSE 0
END) [deferred] ,
MAX(CASE WHEN _event = 'delivered' THEN 1
ELSE 0
END) [delivered] ,
MAX(CASE WHEN _event = 'dropped' THEN 1
ELSE 0
END) [dropped] ,
MAX(CASE WHEN _event = 'group_unsubscribe' THEN 1
ELSE 0
END) [group_unsubscribe] ,
MAX(CASE WHEN _event = 'open' THEN 1
ELSE 0
END) [open] ,
MAX(CASE WHEN _event = 'processed' THEN 1
ELSE 0
END) [processed] ,
MAX(CASE WHEN _event = 'unsubscribe' THEN 1
ELSE 0
END) [unsubscribe]
FROM SendGridStats WITH (NOLOCK)
WHERE summarysent = 0
GROUP BY Campaign_ID ,
Customer_ID ,
email
) sub
ON sub.Customer_ID = main.Customer_ID
AND sub.email = main.email
AND sub.campaign_id = main.campaign_id
WHERE main.campaign_id = @campaign_id;
END;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi Irbrister,
The code looks fine to me. There are other ways also. I think you should be fine with this. Please let me know if your takes time, then will try to improve its performance.
Hope it helps!
/Pawan
The code looks fine to me. There are other ways also. I think you should be fine with this. Please let me know if your takes time, then will try to improve its performance.
Hope it helps!
/Pawan
ASKER
Thanks
ASKER
That is correct. I want to know if it occurred at least once and just flag it as having occurred