Is this query is valid or not? it si giving me error invalid colum name
"exceptCount"
Update lib_RoundPerformance Set Successful = exceptCount select
COUNT(DISTINCT dbo.MSC_ArchivedResult.ResultID) AS exceptCount
FROM dbo.MSC_ArchivedResult INNER JOIN
dbo.lib_RoundPerformance ON
dbo.MSC_ArchivedResult.ArchivedSessionId_fk =
dbo.lib_RoundPerformance.ArchivedSessionID AND
dbo.MSC_ArchivedResult.RoundID =
dbo.lib_RoundPerformance.RoundID
WHERE (dbo.MSC_ArchivedResult.ReadType = 'E')
GROUP BY dbo.MSC_ArchivedResult.ArchivedSessionId_fk,
dbo.MSC_ArchivedResult.RoundID
Any help is appriciated, Thanks in advance.
RikRik,
You have two queries here.
-- First query
Update lib_RoundPerformance Set Successful = exceptCount
-- Second query
select
COUNT(DISTINCT dbo.MSC_ArchivedResult.ResultID) AS exceptCount
FROM dbo.MSC_ArchivedResult INNER JOIN
dbo.lib_RoundPerformance ON
dbo.MSC_ArchivedResult.ArchivedSessionId_fk =
dbo.lib_RoundPerformance.ArchivedSessionID AND
dbo.MSC_ArchivedResult.RoundID =
dbo.lib_RoundPerformance.RoundID
WHERE (dbo.MSC_ArchivedResult.ReadType = 'E')
GROUP BY dbo.MSC_ArchivedResult.ArchivedSessionId_fk,
dbo.MSC_ArchivedResult.RoundID
The first query appears to be invalid, since exceptCount is not
a column of the table lib_RoundPerformance.
Perhaps you mean to do this:
Update dbo.lib_RoundPerformance Set
Successful = (
select COUNT(DISTINCT dbo.MSC_ArchivedResult.ResultID)
FROM dbo.MSC_ArchivedResult
WHERE dbo.MSC_ArchivedResult.ArchivedSessionId_fk =
dbo.lib_RoundPerformance.ArchivedSessionID
AND dbo.MSC_ArchivedResult.RoundID = dbo.lib_RoundPerformance.RoundID
AND dbo.MSC_ArchivedResult.ReadType = 'E'
)
But this is just a guess.
Steve Kass
Drew University
Rik wrote:
>Hello Experts,
>Is this query is valid or not? it si giving me error invalid colum name
>"exceptCount"
>
>Update lib_RoundPerformance Set Successful = exceptCount select
>COUNT(DISTINCT dbo.MSC_ArchivedResult.ResultID) AS exceptCount
>FROM dbo.MSC_ArchivedResult INNER JOIN
> dbo.lib_RoundPerformance ON
>dbo.MSC_ArchivedResult.ArchivedSessionId_fk =
>dbo.lib_RoundPerformance.ArchivedSessionID AND
> dbo.MSC_ArchivedResult.RoundID =
>dbo.lib_RoundPerformance.RoundID
>WHERE (dbo.MSC_ArchivedResult.ReadType = 'E')
>GROUP BY dbo.MSC_ArchivedResult.ArchivedSessionId_fk,
>dbo.MSC_ArchivedResult.RoundID
>
>Any help is appriciated, Thanks in advance.
>Rik
>
>|||A correction. You may want to update only those rows for which
there are matching rows in MSC_ArchivedResult:
Update dbo.lib_RoundPerformance Set
Successful = (
select COUNT(DISTINCT dbo.MSC_ArchivedResult.ResultID)
FROM dbo.MSC_ArchivedResult
WHERE dbo.MSC_ArchivedResult.ArchivedSessionId_fk =
dbo.lib_RoundPerformance.ArchivedSessionID
AND dbo.MSC_ArchivedResult.RoundID = dbo.lib_RoundPerformance.RoundID
AND dbo.MSC_ArchivedResult.ReadType = 'E'
)
where exists (
select * from dbo.MSC_ArchivedResult
WHERE dbo.MSC_ArchivedResult.ArchivedSessionId_fk =
dbo.lib_RoundPerformance.ArchivedSessionID
AND dbo.MSC_ArchivedResult.RoundID = dbo.lib_RoundPerformance.RoundID
AND dbo.MSC_ArchivedResult.ReadType = 'E'
)
SK
Steve Kass wrote:
> Rik,
> You have two queries here.
> -- First query
> Update lib_RoundPerformance Set Successful = exceptCount
>
> -- Second query
> select COUNT(DISTINCT dbo.MSC_ArchivedResult.ResultID) AS exceptCount
> FROM dbo.MSC_ArchivedResult INNER JOIN
> dbo.lib_RoundPerformance ON
> dbo.MSC_ArchivedResult.ArchivedSessionId_fk =
> dbo.lib_RoundPerformance.ArchivedSessionID AND
> dbo.MSC_ArchivedResult.RoundID =
> dbo.lib_RoundPerformance.RoundID
> WHERE (dbo.MSC_ArchivedResult.ReadType = 'E')
> GROUP BY dbo.MSC_ArchivedResult.ArchivedSessionId_fk,
> dbo.MSC_ArchivedResult.RoundID
>
> The first query appears to be invalid, since exceptCount is not
> a column of the table lib_RoundPerformance.
> Perhaps you mean to do this:
> Update dbo.lib_RoundPerformance Set
> Successful = (
> select COUNT(DISTINCT dbo.MSC_ArchivedResult.ResultID)
> FROM dbo.MSC_ArchivedResult
> WHERE dbo.MSC_ArchivedResult.ArchivedSessionId_fk =
> dbo.lib_RoundPerformance.ArchivedSessionID
> AND dbo.MSC_ArchivedResult.RoundID = dbo.lib_RoundPerformance.RoundID
> AND dbo.MSC_ArchivedResult.ReadType = 'E'
> )
> But this is just a guess.
>
> Steve Kass
> Drew University
>
> Rik wrote:
>|||Thanks you steve, You are genious mate.
Your Second Option Works.
Have a good w
end.Ta
Rik
"Steve Kass" <skass@.drew.edu> wrote in message
news:ejZSM41KFHA.3340@.TK2MSFTNGP14.phx.gbl...
> Rik,
> You have two queries here.
> -- First query
> Update lib_RoundPerformance Set Successful = exceptCount
>
> -- Second query
> select COUNT(DISTINCT dbo.MSC_ArchivedResult.ResultID) AS exceptCount
> FROM dbo.MSC_ArchivedResult INNER JOIN
> dbo.lib_RoundPerformance ON
> dbo.MSC_ArchivedResult.ArchivedSessionId_fk =
> dbo.lib_RoundPerformance.ArchivedSessionID AND
> dbo.MSC_ArchivedResult.RoundID =
> dbo.lib_RoundPerformance.RoundID
> WHERE (dbo.MSC_ArchivedResult.ReadType = 'E')
> GROUP BY dbo.MSC_ArchivedResult.ArchivedSessionId_fk,
> dbo.MSC_ArchivedResult.RoundID
>
> The first query appears to be invalid, since exceptCount is not
> a column of the table lib_RoundPerformance.
> Perhaps you mean to do this:
> Update dbo.lib_RoundPerformance Set
> Successful = (
> select COUNT(DISTINCT dbo.MSC_ArchivedResult.ResultID)
> FROM dbo.MSC_ArchivedResult
> WHERE dbo.MSC_ArchivedResult.ArchivedSessionId_fk =
> dbo.lib_RoundPerformance.ArchivedSessionID
> AND dbo.MSC_ArchivedResult.RoundID = dbo.lib_RoundPerformance.RoundID
> AND dbo.MSC_ArchivedResult.ReadType = 'E'
> )
> But this is just a guess.
>
> Steve Kass
> Drew University
>
> Rik wrote:
>
