title: Why/how does this ambiguous UPDATE statement work?
body: <p>Let's say you're running an UPDATE statement on a table, but the information you're putting into this base table is from some other auxiliary table. Normally, you would JOIN the data and not expect the rows in the UPDATE statement's FROM clause to multiply, maintaining that one new row maps to one old row in the base table.</p>
  
  <p>But I was wondering what would happen if your JOIN table was ambiguous somehow, like you couldn't account for each base entity mapping only to one joined entity. Or if you did something nonsensical like join to a base table to a table of its children and updated the base table using that information. How would it choose? Now there are multiple rows per one base table row.</p>
  
  <p>I ran a statement like this in SQL Server 2005 and it seemed to be choosing the first row in each set. But that just seems wrong to me. Shouldn't it fire an error? Why is this desired behavior?</p>
  
  <p><strong>Example code</strong></p>
  
  <pre><code>-- normal
  -- categories are one-to-many bundles
  
  update bundles_denormalized set category = c.description
  
  from bundles_denormalized b
  left join categories c
  on b.category_id = c.id
  
  -- ambiguous
  -- bundles are one-to-many products
  
  update bundles_denormalized set category = p.description
  
  from bundles_denormalized b
  left join products p
  on b.id = p.bundle_id
  </code></pre>