Better way to do this?
-
Farrell,
Sounded like a good idea. I tried:
- UPDATE dbo.Employee
SET CongDist = R.CongDist
FROM dbo.Ranged R
INNER JOIN( SELECT R2.Start, R2.Stop FROM dbo.Ranged R2
- WHERE dbo.Employee.hzip = R2.Zip) Range
WHERE dbo.Employee.hzip4 BETWEEN Range.Start AND Range.Stop
I get an incorrect syntax near the "WHERE".
- UPDATE dbo.Employee
-
Can i ask what was your source for the ZIP+4 database? I've only found ZIP databases, or items like the PLACENAME database with zipcodes from the census, but cannot seem to find a comprehensive zip+4.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Do you have any triggers firing on updating your Employee table? That would really slow performance down to a crawl.
-
- missing the "ON"
- UPDATE dbo.Employee
SET CongDist = R.CongDist
FROM dbo.Ranged R
INNER JOIN( SELECT R2.Start, R2.Stop FROM dbo.Ranged R2
- WHERE dbo.Employee.hzip = R2.Zip) Range
- ON ...... WHERE dbo.Employee.hzip4 BETWEEN Range.Start AND Range.Stop
- Steve
-
Hoo-t:
I tried "ON R.ZIP=Range.Zip" right where you had the "ON ..." in your reply and got a different syntax error:
UPDATE dbo.Employee
SET CongDist = R.CongDist
FROM dbo.Ranged R
INNER JOIN( SELECT R2.Start, R2.Stop FROM dbo.Ranged R2
WHERE dbo.Employee.hzip = R2.Zip) Range
ON R2.ZIP=Range.ZIP
WHERE dbo.Employee.hzip4 BETWEEN Range.Start AND Range.Stop
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'dbo.Employee' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'R2' does not match with a table name or alias name used in the query.
Lowell:
We get our address to ZIP+4 software and database from MelissaData (http://www.melissadata.com). It's their COM Object Suite - databases and code to translate addresses, names and phone numbers. We only use it for ZIP+4 generation. They charge by the number of addresses you're going to process so if you're doing volume it can get expensive.
-
I sometimes get fouled up on joins, and I don't have your schema to test with, but....
UPDATE dbo.Employee
SET CongDist = R.CongDist
FROM dbo.Ranged R
INNER JOIN( SELECT R2.Start, R2.Stop FROM dbo.Ranged R2
WHERE R2.Zip = R.hzip) Range
ON R.ZIP=Range.ZIP
WHERE R.hzip4 BETWEEN Range.Start AND Range.Stop
I think this is right. Give it a try and see what happens. Maybe we can talk Farrell into taking a look at it.
Steve
-
Are your Employee.hzip4, dbo.Ranged.Start, and dbo.Ranged.Stop all four character fields since they are already aligned with the 5 digit zip used in the US?
Also, there is an interesting article in the SQL Server Central daily mailing on Indexes, Clustering Indexes, and Ordering. You might want to look at that and try an ORDER BY clause in your query....
I wasn't born stupid - I had to study.
-
Heh, heh...
Turns out there was nothing wrong with my query, there was a problem on the DB server. Apparently it's been acting oddly for about 2 weeks and our SQL DBA team has spent 2 weeks poking around the server, then finally decided to reboot it. The whole job now runs in 15 seconds.
Now me, I would've rebooted it the first night, but then what do I know besides 30 years in the industry and 15 years managing a large technical services organization?
At this point I'm leaving well enough alone. THanks for all the help guys.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply