title: updating a column from the result of a different query
body: <p>i have tow tables (vehicles and costs) and i have introduced a department column in costs table
  so i want to update the department column in the costs table with department from vehicle table</p>
  
  <p>my vehicle table is like this:</p>
  
  <pre><code>vehicle Department
  S526    424
  D097    402
  D113    403
  D111    430
  C005    400
  C003    403
  C002    403
  </code></pre>
  
  <p>and the costs tavle is like this:</p>
  
  <pre><code>car     Dept
  D097    
  C005    
  D113    
  D113    
  D111    
  C005    
  D113    
  C005    
  C003    
  C002    
  C005    
  S526    
  C002    
  C002
  </code></pre>
  
  <p>How do i update the costs table to have the relevant department</p>
  
  <p>i have tried this but its not working:</p>
  
  <pre><code>update costs as h,
  (
    select v.vid as 'car',
    v.department as 'department',
    h.VID as 'newcar'
    from vehicles AS v
    join costs as h on v.vid = h.VID and v.costcentre = h.CostCenter
  ) as t2
  set h.department = t2.department
  </code></pre>