title: UPDATE FROM in Azure SQL DW?
body: <p>I'm receiving an error in Azure SQL DW trying to do an UPDATE FROM query. The error is "FROM clause in UPDATE and DELETE statements cannot contain subquery sources or joins"</p>
  
  <p>Is this just specific to SQL DW? I don't see anything wrong with this query otherwise. If it is a limitation of SQL DW, what's the alternative?</p>
  
  <pre><code>-- Permanent fact table with 5 billion rows
  CREATE TABLE FactTable (Id1 INT, Id2 INT, EmailAddress NVARCHAR(100), Value1 INT)
  WITH (DISTRIBUTION = HASH(EmailAddress));
  
  -- Staging fact table with 10 million rows    
  CREATE TABLE StageTable (Id1 INT, Id2 INT, EmailAddress NVARCHAR(100), Value1 INT)
  WITH (DISTRIBUTION = HASH(EmailAddress), HEAP);
  
  -- Add a secondary index that should help with joining to StageTable
  CREATE NONCLUSTERED INDEX ix ON FactTable (Id1, Id2);
  
  UPDATE fact
  SET
     Value1 = CASE WHEN stage.Value1 &gt; fact.Value1 THEN stage.Value1 ELSE fact.Value1 END
  FROM FactTable AS fact
  INNER JOIN StageTable AS stage ON fact.Id1 = stage.Id1 AND fact.Id2 = stage.Id2
  </code></pre>