Update query problem

Question

Post reply

Update query problem

vavfam 50321

Mr or Mrs. 500

Points: 511
More actions
April 29, 2016 at 6:55 am

#326055

I am having an issue with trying to update a table with values from another table when a match doesn't exist. Here is the scenario, I have a participation table that has about 250K records. I need to find the legislative districts associated with the records based on a zip + 4 code. My participation table has the zip and the zip4 codes. Sometimes I have both, sometimes neither. Gotta love data sets like this. I have a second table which contains all of the legislative districts by 9 digit zip codes. I have updated the data set and included the individual zip and zip4 codes. Here is the code that I have:
update
[ComEd].[dbo].[PP_FFR_BU]
set
[Zip4] =
(
CASE
when
[Tim].[dbo].[PP_FFR_BU].[Zip] + [Tim].[dbo].[PP_FFR_BU].[Zip4] not in (select zip from [TimCIS].[dbo].[Legis_Districts])
then
CASE
when [Tim].[dbo].[PP_FFR_BU].[Zip] = [TimCIS].[dbo].[Legis_Districts].[Zip5] and [Tim].[dbo].[PP_FFR_BU].[Zip4] <> [TimCIS].[dbo].[Legis_Districts].[Zip4]
then (select top 1 [TimCIS].[dbo].[Legis_Districts].[Zip4] from [TimCIS].[dbo].[Legis_Districts] where [Tim].[dbo].[PP_FFR_BU].[Zip] = [TimCIS].[dbo].[Legis_Districts].[Zip5])
when len([Tim].[dbo].[PP_FFR_BU].[Zip])<5
then '0000'
end
end
)
where
[Tim].[dbo].[PP_FFR_BU].[Zip] + [Tim].[dbo].[PP_FFR_BU].[Zip4] not in (select zip from [TimCIS].[dbo].[Legis_Districts])
Now I am getting an error:
Msg 4104, Level 16, State 1, Line 11 The multi-part identifier "TimCIS.dbo.Legis_Districts.Zip4" could not be bound. Which equates to the when clause in the second CASE statement.
If the zip and zip4 in the original table are in the legislative district table then I need to move to the next record. If the zip is in the table and the zip4 doesn't match, I need the first value that matches. If the zip doesn't exist in the legislative table then I want to put a '0000' in the zip4 of the participation table. Any insight would be greatly appreciated.

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 1

Quick question, do you have a database called TimCIS?

😎

vavfam 50321 Mr or Mrs. 500 Points: 511 More actions · Answer 2

I renamed it for the purpose of the question. I have two databases on the same server. One is the main production database holding all of our transactional data and the other consists of external customer data records which we get from our corporate offices. The legislative_districts table is a third party data set that marries the nine digit zip code to different legislative districts in our state.

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 3

The error message states that the table/view "TimCIS.dbo.Legis_Districts.Zip4" cannot be found, check the actual database and object names and make certain you can run queries like

SELECT COUNT(*)

FROM TimCIS.dbo.Legis_Districts.Zip4;

😎

vavfam 50321 Mr or Mrs. 500 Points: 511 More actions · Answer 4

Yes when I run the query against the table [TimCIS].[dbo].[Legis_Districts] I get 2,552,381 records.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Probably Zip4 is an invalid column, then. Do you get a similar error for this?

SELECT TOP 1 Zip4

FROM TimCIS.dbo.Legis_Districts

John

vavfam 50321 Mr or Mrs. 500 Points: 511 More actions · Answer 6

Legis_Districts.Zip4 is a good column. I think I see where it might get confused though. The top 1 I need is where the zip5 field is equal to a specific 5 digit code.

SELECT TOP 1 Zip4

FROM TimCIS.dbo.Legis_Districts

where Zip5 = '60004'

I don't think I am doing that part properly which would cause my problem perhaps.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

Good - does that work now, then? By the way, don't use TOP without an ORDER BY clause, since there's no guarantee which row you'll get.

John

vavfam 50321 Mr or Mrs. 500 Points: 511 More actions · Answer 8

John and Eirikur

Could I also have an issue in the syntax because I have

CASE

when

[Tim].[dbo].[PP_FFR_BU].[Zip] + [Tim].[dbo].[PP_FFR_BU].[Zip4] not in (select zip from [TimCIS].[dbo].[Legis_Districts])

and

where

[Tim].[dbo].[PP_FFR_BU].[Zip] + [Tim].[dbo].[PP_FFR_BU].[Zip4] not in (select zip from [TimCIS].[dbo].[Legis_Districts])

I am causing an issue by having the where clause and the case clause being the same thing?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

Well, yes - since you've tested in the WHERE clause that that condition is true, you don't need to test again with a CASE expression. But there's no syntax error in that part that I can see - just unnecessary extra complexity.

John

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 10

If that is the exact query you're running, then I imagine it's this line:

when [Tim].[dbo].[PP_FFR_BU].[Zip] = [TimCIS].[dbo].[Legis_Districts].[Zip5] and [Tim].[dbo].[PP_FFR_BU].[Zip4] <> [TimCIS].[dbo].[Legis_Districts].[Zip4]

The TimCIS.dbo.Legis_Districts table is not in a FROM clause for the UPDATE, so it can't be referenced except in a subquery that does specify that table in a FROM clause.

Cheers!