title: Update table values based on a subquery from another database
body: <p>I'm trying to dynamically subquery another database (db2) and update current table (db1.t1) based on the results.  The field Location (db1.t1.location) determines which database (db2) should be.  I typically accomplish this using vba however, I'm looping through each row.  Quite simply i'm 100% the sql equivalent would be much more efficient.</p>
  
  <p>What i'd like to do is have 2 databases</p>
  
  <ol>
  <li><p>DB1</p></li>
  <li><p>DB2 (where the database would be selected based on value in DB1)</p></li>
  </ol>
  
  <p>e.g. if db1.dbo.t1.location='North' then DB2 name='DBNorth', if db1.dbo.t1.location='South' then DB2 name='DBSouth'.</p>
  
  <p>Not sure of where to begin to join / update fields.</p>
  
  <p>The following is what i'm trying to accomplish:</p>
  
  <ul>
  <li>Establish which database will be joined based on value in row of db1.dbo.t1</li>
  <li>join databases based on location, unique_identifier (db1.dbo.t1.location, 
  db1.dbo.t1.unique_identifer)</li>
  <li>if record exists in db2 that contains location, unique identifer from db1 then 
  update db1.dbo.t1.status to 1 else 0</li>
  </ul>
  
  <p>All help is appreciated!</p>