I have three tables, [Versions], [RCF Numbers] and [Call Counts]. [Versions]
is in a one to many relationship with [RCF Numbers], and [RCF Numbers] is on
e
to many with [Call Counts].
I need to update [Versions].[Number Months Reported] with the highest value
in [Call Counts].[Month Number] (technically, the highest value – 3), but
when I write a stored procedure to do this I get an error saying the
procedure is not updateable because the underlying query contains aggregates
.
Is there a way around this?
Here's the relevant table structure:
################################
CREATE TABLE [Versions] (
[Version ID] [int] IDENTITY (1, 1) NOT NULL ,
[Test ID] [int] NULL CONSTRAINT [DF__Versions__Test I__6E8B6712] DEFAULT (0),
[Version Type] [smallint] NULL ,
[Number Months Reported] [int] NULL CONSTRAINT [DF_Versions_Number Months
Reported] DEFAULT (0),
(
[Version ID]
) ON [PRIMARY] ,
CONSTRAINT [Versions_FK00] FOREIGN KEY
(
[Test ID]
) REFERENCES [Tests] (
[Test ID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
################################
CREATE TABLE [RCF Numbers] (
[RCF Number ID] [int] IDENTITY (1, 1) NOT NULL ,
[Version ID] [int] NULL ,
[RCF Number] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Termination Number] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
CONSTRAINT [PK_RCF Numbers] PRIMARY KEY CLUSTERED
(
[RCF Number ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_RCF Numbers_Versions] FOREIGN KEY
(
[Version ID]
) REFERENCES [Versions] (
[Version ID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
################################
CREATE TABLE [Call Counts] (
[Call Count ID] [int] IDENTITY (1, 1) NOT NULL ,
[RCF Number ID] [int] NULL ,
[Month Number] [smallint] NULL ,
CONSTRAINT [PK_Call Counts] PRIMARY KEY CLUSTERED
(
[Call Count ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Call Counts_RCF Numbers] FOREIGN KEY
(
[RCF Number ID]
) REFERENCES [RCF Numbers] (
[RCF Number ID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
################################
Here's a view to query the correct value:
CREATE VIEW dbo.vwCalcMonthsReported
AS
SELECT MAX(dbo.[Call Counts].[Month Number]) AS [Months of Data],
dbo.Versions.[Number Months Reported], dbo.[RCF Numbers].[Version ID]
FROM dbo.Versions INNER JOIN
dbo.[RCF Numbers] ON dbo.Versions.[Version ID] =
dbo.[RCF Numbers].[Version ID] INNER JOIN
dbo.[Call Counts] ON dbo.[RCF Numbers].[RCF Number ID]
= dbo.[Call Counts].[RCF Number ID]
GROUP BY dbo.Versions.[Number Months Reported], dbo.[RCF Numbers].[Version I
D]
HAVING (MAX(dbo.[Call Counts].[Month Number]) > 3)
GO
################################
Here's my stored procedure:
CREATE PROCEDURE dbo.sp_Update_Months_Reported
AS UPDATE dbo.vwCalcMonthsReported
SET [Number Months Reported] = [Months of Data] - 3
GO
#####################Hi Mike,
You could consider an Instead Of trigger on the view for your updates.
http://msdn.microsoft.com/library/d...>
nsteadof.asp
Cheers,
Steve Goodyear
Vancouver, BC
"mike" wrote:
> I have three tables, [Versions], [RCF Numbers] and [Call Counts]. [Versions]
> is in a one to many relationship with [RCF Numbers], and [RCF Numbers] is
one
> to many with [Call Counts].
> I need to update [Versions].[Number Months Reported] with the highest value
> in [Call Counts].[Month Number] (technically, the highest value – 3), bu
t
> when I write a stored procedure to do this I get an error saying the
> procedure is not updateable because the underlying query contains aggregat
es.
> Is there a way around this?
> Here's the relevant table structure:
> ################################
> CREATE TABLE [Versions] (
> [Version ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Test ID] [int] NULL CONSTRAINT [DF__Versions__Test I__6E8B6712] DEFAULT (0),
> [Version Type] [smallint] NULL ,
> [Number Months Reported] [int] NULL CONSTRAINT [DF_Versions_Number Months
> Reported] DEFAULT (0),
> (
> [Version ID]
> ) ON [PRIMARY] ,
> CONSTRAINT [Versions_FK00] FOREIGN KEY
> (
> [Test ID]
> ) REFERENCES [Tests] (
> [Test ID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> ################################
> CREATE TABLE [RCF Numbers] (
> [RCF Number ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Version ID] [int] NULL ,
> [RCF Number] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Termination Number] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> CONSTRAINT [PK_RCF Numbers] PRIMARY KEY CLUSTERED
> (
> [RCF Number ID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_RCF Numbers_Versions] FOREIGN KEY
> (
> [Version ID]
> ) REFERENCES [Versions] (
> [Version ID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
> GO
> ################################
> CREATE TABLE [Call Counts] (
> [Call Count ID] [int] IDENTITY (1, 1) NOT NULL ,
> [RCF Number ID] [int] NULL ,
> [Month Number] [smallint] NULL ,
> CONSTRAINT [PK_Call Counts] PRIMARY KEY CLUSTERED
> (
> [Call Count ID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Call Counts_RCF Numbers] FOREIGN KEY
> (
> [RCF Number ID]
> ) REFERENCES [RCF Numbers] (
> [RCF Number ID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
> GO
> ################################
> Here's a view to query the correct value:
> CREATE VIEW dbo.vwCalcMonthsReported
> AS
> SELECT MAX(dbo.[Call Counts].[Month Number]) AS [Months of Data],
> dbo.Versions.[Number Months Reported], dbo.[RCF Numbers].[Version ID]
> FROM dbo.Versions INNER JOIN
> dbo.[RCF Numbers] ON dbo.Versions.[Version ID] =
> dbo.[RCF Numbers].[Version ID] INNER JOIN
> dbo.[Call Counts] ON dbo.[RCF Numbers].[RCF Number I
D]
> = dbo.[Call Counts].[RCF Number ID]
> GROUP BY dbo.Versions.[Number Months Reported], dbo.[RCF Numbers].[Version
ID]
> HAVING (MAX(dbo.[Call Counts].[Month Number]) > 3)
> GO
> ################################
> Here's my stored procedure:
> CREATE PROCEDURE dbo.sp_Update_Months_Reported
> AS UPDATE dbo.vwCalcMonthsReported
> SET [Number Months Reported] = [Months of Data] - 3
> GO
> #####################
>|||Your view has aggregate funtions in select list. So you can not update it
with stored procedure. You can create INSTEAD OF triggers.
"mike" wrote:
> I have three tables, [Versions], [RCF Numbers] and [Call Counts]. [Versions]
> is in a one to many relationship with [RCF Numbers], and [RCF Numbers] is
one
> to many with [Call Counts].
> I need to update [Versions].[Number Months Reported] with the highest value
> in [Call Counts].[Month Number] (technically, the highest value – 3), bu
t
> when I write a stored procedure to do this I get an error saying the
> procedure is not updateable because the underlying query contains aggregat
es.
> Is there a way around this?
> Here's the relevant table structure:
> ################################
> CREATE TABLE [Versions] (
> [Version ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Test ID] [int] NULL CONSTRAINT [DF__Versions__Test I__6E8B6712] DEFAULT (0),
> [Version Type] [smallint] NULL ,
> [Number Months Reported] [int] NULL CONSTRAINT [DF_Versions_Number Months
> Reported] DEFAULT (0),
> (
> [Version ID]
> ) ON [PRIMARY] ,
> CONSTRAINT [Versions_FK00] FOREIGN KEY
> (
> [Test ID]
> ) REFERENCES [Tests] (
> [Test ID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> ################################
> CREATE TABLE [RCF Numbers] (
> [RCF Number ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Version ID] [int] NULL ,
> [RCF Number] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Termination Number] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> CONSTRAINT [PK_RCF Numbers] PRIMARY KEY CLUSTERED
> (
> [RCF Number ID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_RCF Numbers_Versions] FOREIGN KEY
> (
> [Version ID]
> ) REFERENCES [Versions] (
> [Version ID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
> GO
> ################################
> CREATE TABLE [Call Counts] (
> [Call Count ID] [int] IDENTITY (1, 1) NOT NULL ,
> [RCF Number ID] [int] NULL ,
> [Month Number] [smallint] NULL ,
> CONSTRAINT [PK_Call Counts] PRIMARY KEY CLUSTERED
> (
> [Call Count ID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Call Counts_RCF Numbers] FOREIGN KEY
> (
> [RCF Number ID]
> ) REFERENCES [RCF Numbers] (
> [RCF Number ID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
> GO
> ################################
> Here's a view to query the correct value:
> CREATE VIEW dbo.vwCalcMonthsReported
> AS
> SELECT MAX(dbo.[Call Counts].[Month Number]) AS [Months of Data],
> dbo.Versions.[Number Months Reported], dbo.[RCF Numbers].[Version ID]
> FROM dbo.Versions INNER JOIN
> dbo.[RCF Numbers] ON dbo.Versions.[Version ID] =
> dbo.[RCF Numbers].[Version ID] INNER JOIN
> dbo.[Call Counts] ON dbo.[RCF Numbers].[RCF Number I
D]
> = dbo.[Call Counts].[RCF Number ID]
> GROUP BY dbo.Versions.[Number Months Reported], dbo.[RCF Numbers].[Version
ID]
> HAVING (MAX(dbo.[Call Counts].[Month Number]) > 3)
> GO
> ################################
> Here's my stored procedure:
> CREATE PROCEDURE dbo.sp_Update_Months_Reported
> AS UPDATE dbo.vwCalcMonthsReported
> SET [Number Months Reported] = [Months of Data] - 3
> GO
> #####################
>|||On Thu, 7 Apr 2005 08:37:03 -0700, mike wrote:
>I have three tables, [Versions], [RCF Numbers] and [Call Counts]. [Versions]
>is in a one to many relationship with [RCF Numbers], and [RCF Numbers] is o
ne
>to many with [Call Counts].
>I need to update [Versions].[Number Months Reported] with the highest value
>in [Call Counts].[Month Number] (technically, the highest value 3), but
>when I write a stored procedure to do this I get an error saying the
>procedure is not updateable because the underlying query contains aggregate
s.
>Is there a way around this?
Hi Mike,
It's hard to say if this will work without having any sample data to
test it on (see www.aspfaq.com/5006), but you might try if this update
statement does the trick:
UPDATE dbo.Versions
SET [Number Months Reported] =
(SELECT MAX(dbo.[Call Counts].[Month Number])
FROM dbo.[RCF Numbers] AS rn
INNER JOIN dbo.[Call Counts] AS cc
ON cc.[RCF Number ID] = rn.[RCF Number ID]
WHERE rn.[Version ID] = dbo.Versions.[Version ID]) - 3
WHERE (SELECT MAX(dbo.[Call Counts].[Month Number])
FROM dbo.[RCF Numbers] AS rn
INNER JOIN dbo.[Call Counts] AS cc
ON cc.[RCF Number ID] = rn.[RCF Number ID]
WHERE rn.[Version ID] = dbo.Versions.[Version ID]) > 3
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo. I needed to tweak it a bit, but your sp works great!
"Hugo Kornelis" wrote:
> On Thu, 7 Apr 2005 08:37:03 -0700, mike wrote:
>
> Hi Mike,
> It's hard to say if this will work without having any sample data to
> test it on (see www.aspfaq.com/5006), but you might try if this update
> statement does the trick:
> UPDATE dbo.Versions
> SET [Number Months Reported] =
> (SELECT MAX(dbo.[Call Counts].[Month Number])
> FROM dbo.[RCF Numbers] AS rn
> INNER JOIN dbo.[Call Counts] AS cc
> ON cc.[RCF Number ID] = rn.[RCF Number ID]
> WHERE rn.[Version ID] = dbo.Versions.[Version ID]) - 3
> WHERE (SELECT MAX(dbo.[Call Counts].[Month Number])
> FROM dbo.[RCF Numbers] AS rn
> INNER JOIN dbo.[Call Counts] AS cc
> ON cc.[RCF Number ID] = rn.[RCF Number ID]
> WHERE rn.[Version ID] = dbo.Versions.[Version ID]) > 3
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
