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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 UPDATE with subquery results
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

3 Posts

Posted - 11/10/2010 :  16:16:11  Show Profile  Reply with Quote

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')  

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

UPDATE crime_table_08
SET County = (
	SELECT TOP 1 county
		(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(
       				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?

Flowing Fount of Yak Knowledge

6065 Posts

Posted - 11/10/2010 :  17:01:49  Show Profile  Reply with Quote
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(
       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'

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

Edited by - TG on 11/10/2010 17:05:47
Go to Top of Page

Aged Yak Warrior

700 Posts

Posted - 11/11/2010 :  05:14:26  Show Profile  Reply with Quote
Something like the following will work in SQL2000:

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

SET county = D.CountryName
FROM crime_table_08 C
		SELECT T.statename, T.CityName, MIN(T.CountryName) AS CountryName
		FROM #t T
				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 = D.CityName


Go to Top of Page

Starting Member

3 Posts

Posted - 11/11/2010 :  14:33:09  Show Profile  Reply with Quote
Thank You Ifor, your code worked brilliantly.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000