Update Table With Different Values

1

Hi. I want to update the value in the Runners field to match the actual number of competitors that took part in the event. So, for Event_Id 1 the Runners should change from 5 to 4, Event_Id 2 the Runners field should change from 6 to 4. Where the number of distinct Competitors matches the value of the Runners field then no change is needed.

GO
CREATE TABLE [dbo].[Runners_Temp](
	[RUNNERS_ID] [int] IDENTITY(1,1) NOT NULL,
	[RUNNERS] [float] NULL,
	[EVENT_ID] [float] NULL,
	[COMPETITOR] [int] NULL,
 CONSTRAINT [PK_Runners_Temp] PRIMARY KEY CLUSTERED 
(
	[RUNNERS_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Runners_Temp] ON 

INSERT [dbo].[Runners_Temp] ([RUNNERS_ID], [RUNNERS], [EVENT_ID], [COMPETITOR]) VALUES (1, 5, 1, 56)
INSERT [dbo].[Runners_Temp] ([RUNNERS_ID], [RUNNERS], [EVENT_ID], [COMPETITOR]) VALUES (2, 5, 1, 785)
INSERT [dbo].[Runners_Temp] ([RUNNERS_ID], [RUNNERS], [EVENT_ID], [COMPETITOR]) VALUES (3, 5, 1, 121)
INSERT [dbo].[Runners_Temp] ([RUNNERS_ID], [RUNNERS], [EVENT_ID], [COMPETITOR]) VALUES (4, 5, 1, 6)
INSERT [dbo].[Runners_Temp] ([RUNNERS_ID], [RUNNERS], [EVENT_ID], [COMPETITOR]) VALUES (5, 6, 2, 89)
INSERT [dbo].[Runners_Temp] ([RUNNERS_ID], [RUNNERS], [EVENT_ID], [COMPETITOR]) VALUES (6, 6, 2, 265)
INSERT [dbo].[Runners_Temp] ([RUNNERS_ID], [RUNNERS], [EVENT_ID], [COMPETITOR]) VALUES (7, 6, 2, 58)
INSERT [dbo].[Runners_Temp] ([RUNNERS_ID], [RUNNERS], [EVENT_ID], [COMPETITOR]) VALUES (8, 6, 2, 11)
INSERT [dbo].[Runners_Temp] ([RUNNERS_ID], [RUNNERS], [EVENT_ID], [COMPETITOR]) VALUES (9, 4, 3, 56)
INSERT [dbo].[Runners_Temp] ([RUNNERS_ID], [RUNNERS], [EVENT_ID], [COMPETITOR]) VALUES (10, 4, 3, 74)
INSERT [dbo].[Runners_Temp] ([RUNNERS_ID], [RUNNERS], [EVENT_ID], [COMPETITOR]) VALUES (11, 4, 3, 75)
INSERT [dbo].[Runners_Temp] ([RUNNERS_ID], [RUNNERS], [EVENT_ID], [COMPETITOR]) VALUES (12, 4, 3, 119)
SET IDENTITY_INSERT [dbo].[Runners_Temp] OFF
GO

`
Thanks for all and any help
2

;with cte as (select Event_id, count(distinct Competitor) as NumRunners
from #r
group by Event_id)

update r set Runners = c.NumRunners
from #r r
join cte c
on r.Event_ID = c.Event_ID
and r.Runners <> c.NumRunners

3

Thank you so much for doing that for me. It works like a charm.