I am using SQL Server 2005 and I would like to update my tables as follows.

Table1 (it has three fields I'm working with)
CustomerID
SiteID
SiteCount

Table2 (this table is a list of sites)
SiteID

In the data of Table1
CustomerID / SiteID / SiteCount
1 / 6 / 1
4 / 15 / 1
7 / 18 / 1
7 / 42 / 2
7 / 112 / 3

In the data of Table2
SiteID
6
15
18
42
112


In this data I have 5 records in Table1. There are three records with a
CustomerID of 7. Notice that the SiteID on these three records are
different.
I would like to write a SQL command to fill in the SiteCount field where it
shows a number which is a count of records found in Table2 with the same
SiteID.
The command needs to actually update the SiteCount field within Table1.

I am fairly new to SQL so I really appreciate the .


--
Thanks,

Dan Tallent

Here is one way:

UPDATE Table1
SET SiteCount = (SELECT COUNT(*)
FROM Table2 AS T
WHERE T.SiteID = Table1.SiteID);

--
Plamen Ratchev
http://www.SQLStudio.com

No, this won't work. All this will do is set Table1.SiteCount = 1 for
every record.


However, I did find something that does appear to work.
WITH TBL
AS
(
select count(*) LineNumber, Table1B.CustomerID, Table1B.SiteID,
Table1B.Table1ID
from Table1 Table1A
join Table1 Table1B on Table1A.SiteID >= Table1B.SiteID
and Table1A.CustomerID = Table1B.CustomerID
group by Table1B.CustomerID, Table1B.SiteID, Table1B.Table1ID
)

update Table1
set Table1.SiteCount = TBL.LineNumber
from TBL where Table1.Table1ID = TBL.Table1ID






"Plamen Ratchev" <Plamen@SQLStudio.com> wrote in message
news:vPqdnR5GH_wC8VzVnZ2dnUVZ_r3inZ2d@speakeasy.ne t...
> Here is one way:
>
> UPDATE Table1
> SET SiteCount = (SELECT COUNT(*)
> FROM Table2 AS T
> WHERE T.SiteID = Table1.SiteID);
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com

Those are completely different requirements than what you requested
initially:
"...count of records found in Table2 with the same SiteID..."

Plus now you added new column Table1ID...

If you just need to update with ranking value, then this is a better
approach:

;WITH Ranked
AS
(SELECT SiteCount,
ROW_NUMBER() OVER(PARTITION BY CustomerID
ORDER BY SiteID) AS seq
FROM Table1)
UPDATE Ranked
SET SiteCount = seq;

Plus you really do not need to update, you can always query and have
ROW_NUMBER recalculate the ranking dynamically.

--
Plamen Ratchev
http://www.SQLStudio.com

Ok.. I understand the confusion... I should of said "counter" instead of
"count"
I was worried I used the wrong verbage which is why I included the sample
data.

I will check out this method...

Thanks
Dan




"Plamen Ratchev" <Plamen@SQLStudio.com> wrote in message
news:vPqdnRhGH_xk5lzVnZ2dnUVZ_r3inZ2d@speakeasy.ne t...
> Those are completely different requirements than what you requested
> initially:
> "...count of records found in Table2 with the same SiteID..."
>
> Plus now you added new column Table1ID...
>
> If you just need to update with ranking value, then this is a better
> approach:
>
> ;WITH Ranked
> AS
> (SELECT SiteCount,
> ROW_NUMBER() OVER(PARTITION BY CustomerID
> ORDER BY SiteID) AS seq
> FROM Table1)
> UPDATE Ranked
> SET SiteCount = seq;
>
> Plus you really do not need to update, you can always query and have
> ROW_NUMBER recalculate the ranking dynamically.
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com