title: I am trying to update a table based on matches to another table, getting a multi-part identifier error
body: <p>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:</p>
  
  <pre><code>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] &lt;&gt; [TimCIS].[dbo].[Legis_Districts].[Zip4]
              then (select top 1 [ComEdCIS].[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])&lt;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])
  </code></pre>
  
  <p>Now I am getting an error:</p>
  
  <blockquote>
    <p>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.  </p>
  </blockquote>
  
  <p>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.</p>