On Jun 11, 9:08 am, Charlie <Char...@discussions.microsoft.com> wrote:
> I am trying to run an update to a table that requies a count from another
> table and want the update to be where the Year & Month are equal to each
> other. I am trying to work with a poorly set up table structure and am
> having a hard time trying to figure out how to get an update to work form a
> subquery (not good with subqueries, still new to this)
>
> Table Structure to be updated:
>
> tblQuestions
> (
> Year int
> ,Month int
> ,InfoReceived int
> )
>
> Here is an example of the "subquery":
>
> SELECT COUNT(tblCustomers.CustomerID)
> FROM Table1 INNER JOIN
> tblQuestions ON tblCustomers.Customer.ID = tblQuestions.ID
> WHERE (tblQuestions.InfoReceived = 1)
> GROUP BY DATEPART(Year, tbl.Customers.ADDDate), DATEPART(Month,
> tbl.Customers.ADDDate.EffDate))
>
> I want to update tblQuestions with the Count from the above query, but it
> needs to update where the DATEPART(Year, tbl.Customers.ADDDate) = Year, AND
> DATEPART(Month, tbl.Customers.ADDDate.EffDate) = Month
>
> How do I do this? I have tried several things and am just not having a luck
> what so ever. (two days now!) And just can not find an example of doing
> what I want to do. Is this not possible?
>
> Any help is so greatly appreciated.

"M A Srinivas" wrote:

> On Jun 11, 9:08 am, Charlie <Char...@discussions.microsoft.com> wrote:
> > I am trying to run an update to a table that requies a count from another
> > table and want the update to be where the Year & Month are equal to each
> > other. I am trying to work with a poorly set up table structure and am
> > having a hard time trying to figure out how to get an update to work form a
> > subquery (not good with subqueries, still new to this)
> >
> > Table Structure to be updated:
> >
> > tblQuestions
> > (
> > Year int
> > ,Month int
> > ,InfoReceived int
> > )
> >
> > Here is an example of the "subquery":
> >
> > SELECT COUNT(tblCustomers.CustomerID)
> > FROM Table1 INNER JOIN
> > tblQuestions ON tblCustomers.Customer.ID = tblQuestions.ID
> > WHERE (tblQuestions.InfoReceived = 1)
> > GROUP BY DATEPART(Year, tbl.Customers.ADDDate), DATEPART(Month,
> > tbl.Customers.ADDDate.EffDate))
> >
> > I want to update tblQuestions with the Count from the above query, but it
> > needs to update where the DATEPART(Year, tbl.Customers.ADDDate) = Year, AND
> > DATEPART(Month, tbl.Customers.ADDDate.EffDate) = Month
> >
> > How do I do this? I have tried several things and am just not having a luck
> > what so ever. (two days now!) And just can not find an example of doing
> > what I want to do. Is this not possible?
> >
> > Any help is so greatly appreciated.
>
> You are almost there
> UPDATE A SET
> InfoReceived = B.InfoReceived
> FROM tblQuestions A,
> ( SELECT COUNT(tblCustomers.CustomerID) as
> InfoReceived ,DATEPART(Year, tbl.Customers.ADDDate) as CustYear,
> DATEPART(Month,
> tbl.Customers.ADDDate.EffDate) as CustMonth
> FROM Table1 INNER JOIN
> tblQuestions ON tblCustomers.Customer.ID = tblQuestions.ID
> WHERE (tblQuestions.InfoReceived = 1)
> GROUP BY DATEPART(Year, tbl.Customers.ADDDate),
> DATEPART(Month,
> tbl.Customers.ADDDate.EffDate) ) AS B
> A.[Year] = B.CustYear
> AND A.[Month] = B.CustMonth
>
> I suggest avoid naming your columns Year, Month as these are SQL
> Server Keywords
>
>
>