Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 UPDATE with subquery results

Author  Topic 

royhink
Starting Member

3 Posts

Posted - 2010-11-10 : 16:16:11


I know enough SQL to write basic SELECT and UPDATEs. I'm struggling with JOINs

I am on WIN Server 2003/SQL2000

I want to update the 'county' field in a crime table. I am using a zip_xref table that will give me the county names. The issue is that several hundred cities are in multiple counties, and I want to update the table with the most prevalent county, so my sub-query must have a way to rank the counties by size.

I've written this to find the county names ranked by the number of zip codes in each county.

SELECT DISTINCT CountyName AS counties, COUNT(*) AS numZips
FROM Zip_XRef WHERE (CityName = 'Aurora') AND (StateName = 'Colorado')
GROUP BY CountyName ORDER BY numZips DESC


I know this UPDATE doesn't work, it's just the idea.


UPDATE crime_table_08
SET County = (
SELECT TOP 1 county
FROM
(CREATE TABLE #countyTemp (
county VARCHAR(255),
numZips int IDENTITY (1, 1)
)
INSERT INTO #countyTemp (county, numZips)
SELECT CountyName, COUNT(*)
FROM Zip_XRef AS z, crime_table_08 AS c
WHERE RTRIM(z.StateName) = RTRIM(c.State) AND RTRIM(z.CityName) = RTRIM(c.city)
GROUP BY CountyName
)
)
DROP TABLE #countyTemp


I'm thinking that I'll need to create a temporary table, and then call it to update c, and I can't quite get my head around the logic. Any help greatly appreciated.

OR, Does anyone think there is a better way?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-11-10 : 17:01:49
One thing - you don't need the DISTINCT in your first query because you are GROUPing BY CountyName so it is already distinct.


update ct set
ct.county = ca.countyname
from crime_table_08 ct
cross apply (
select top 1 countyname
from zip_xref
where rtrim(statename) = rtrim(ct.state)
and rtrim(CityName) = rtrim(ct.city)
group by countyname
order by count(*) desc
) ca

--not needed unless you want to limit the update to just this city/state
where ct.CityName = 'Aurora'
and ct.StateName = 'Colorado'


EDIT:
Woops - I just realized you posted this in sql server 2000 forum. CROSS APPLY is not available in sql server 2000.

I'll update this to 2000 code in a sec...

Be One with the Optimizer
TG
Go to Top of Page

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-11-11 : 05:14:26
Something like the following will work in SQL2000:

SELECT Z.statename, Z.CityName, Z.CountryName, COUNT(*) AS NumZips
INTO #t
FROM zip_xref Z
GROUP BY Z.statename, Z.CityName, Z.CountryName

UPDATE C
SET county = D.CountryName
FROM crime_table_08 C
JOIN
(
SELECT T.statename, T.CityName, MIN(T.CountryName) AS CountryName
FROM #t T
JOIN
(
SELECT T1.statename, T1.CityName, MAX(NumZips) AS NumZips
FROM #t T1
GROUP BY T1.statename, T1.CityName
) D1
ON T.statename = D1.statename
AND T.CityName = D1.CityName
AND T.NumZips = D1.NumZips
GROUP BY T.statename, T.CityName
) D
ON C.state = D.statename
AND C.city = D.CityName

DROP TABLE #t

Go to Top of Page

royhink
Starting Member

3 Posts

Posted - 2010-11-11 : 14:33:09
Thank You Ifor, your code worked brilliantly.
Go to Top of Page
   

- Advertisement -