title: Why doesn't SQL Server fail when the result of an UPDATE is ambiguous?
body: <p>I have two tables, a destination for the update:</p>
  
  <pre><code>create table dest (value int)
  insert into dest values (0)
  </code></pre>
  
  <p>and a source:</p>
  
  <pre><code>create table source (value int)
  insert into source values (4)
  insert into source values (1)
  </code></pre>
  
  <p>If I run this query:</p>
  
  <pre><code>UPDATE dest SET value = (select value from source WHERE 1=1)
  </code></pre>
  
  <p>SQL Server fails with:</p>
  
  <pre><code>Subquery returned more than 1 value. This is not permitted when 
  the subquery follows =, !=, &lt;, &lt;= , &gt;, &gt;= ...
  </code></pre>
  
  <p>which is perfect.  But if I run this query:</p>
  
  <pre><code>UPDATE dest SET value = source.value FROM dest INNER JOIN Source ON 1=1 
  </code></pre>
  
  <p>... it picks randomly one of the values from source and updates dest with it.</p>
  
  <p>Scary?  Is there an explanation for this?</p>