SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

royhink
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')  
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
Flowing Fount of Yak Knowledge

USA
6062 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(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

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

Ifor
Aged Yak Warrior

583 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
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 - 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  
 New 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.08 seconds. Powered By: Snitz Forums 2000