Last post Jul 20, 2011 03:24 AM by Sgt8log
Jul 19, 2011 11:19 PM|gyana.ojha|LINK
I need help in a update query
I have a2 table
First table is Master table contains column as below
ID RuleID Count
1 1 2
Second Table is a Reference Table contains column as
ID FKRuleID ISActive
1 1 True
2 1 True
3 1 False
In above scenario I am updateting master table by taking count of Active FKRule from reference table . As you can see there is two true in active column for FKRuleID 1 that why count gets updated as 2.
Now my requirement is if in reference table there is no active column is true then it should updated count as 0.
Jul 20, 2011 02:07 AM|Jaisendme|LINK
SET T1.Count = T2.Count(IsActive)
FROM MasterTable T1, ReferenceTable T2
Left join ON T1.RuleId = T2.FKRuleId
Where T2.IsActive = true
Jul 20, 2011 03:24 AM|Sgt8log|LINK
Use Triggers to update the column.
This way you dont need to do anything on your presentation layer.
I replicated it like this.
CREATE TRIGGER [dbo].[Update_Count_Column]
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @ID INT
SELECT @ID = FKRuleID
-- Insert statements for trigger here
SET [Count] = (SELECT COUNT(*) FROM [dbo].[Reference] WHERE IsActive = 1)
WHERE RuleId = @ID