title: SQL Merge (with Temp Table) failing on SubQuery returned more than 1 value (But I am not using a Sub-Query.. in the update)
body: <p>I am hoping someone can help me out of this tedium...!?</p>
  
  <p>As the title suggests I have a Temp Table (create dynamically in a select statement):</p>
  
  <pre><code>SELECT *
  INTO #results
  FROM Table_1 
  CROSS APPLY (   SELECT TOP 1 *
                  FROM Table_2
                  WHERE (Table_1.ItemId = Table_2.ItemId)
                  ORDER BY CreatedDt DESC
               )
  </code></pre>
  
  <p>... which as you can see uses a Sub-Query in a cross join.</p>
  
  <p>Next I am trying to use this temp table <strong>#results</strong> to update a related table with its values. have tried using an update:</p>
  
  <pre><code>UPDATE a
  SET a.StatusId = b.StatusId
  FROM Table_1    a
  INNER JOIN #results     b on (a.ItemId = b.ItemId)
  </code></pre>
  
  <p>and with a Merge:</p>
  
  <pre><code>MERGE INTO Table_1 a
  USING #results b
  ON (a.ItemId = b.temId)
  WHEN MATCHED THEN UPDATE SET a.StatusId = b.StatusId;
  </code></pre>
  
  <p>but I seem to always get a response:</p>
  
  <blockquote>
    <p><em>Msg 512, Level 16, State 1, Procedure trg_dbo_PBITree_TreeModel_HierarchicalEscalationHistory_InsertNode,
    Line 7 [Batch Start Line 11] Subquery returned more than 1 value. This
    is not permitted when the subquery follows =, !=, &lt;, &lt;= , >, >= or
    when the subquery is used as an expression.</em></p>
  </blockquote>
  
  <p>When I query the two tables in question (#results &amp; Table_1) they both have 1 to 1 relationships and cannot see at all where it could be hiding some kind of Subquery!?</p>
  
  <p>Can anyone help quickly on this at all please? This seems to be 1-0-1 stuff and its baking my burger!</p>
  
  <p>-- Edit --</p>
  
  <p>I have taken a look at the Trigger mentioned in the error message as it was suggested it could be trying to handle a single row update instead of a multiple row update which is what I am doing. Nothing looking too unusual to me...?</p>
  
  <pre><code>ALTER TRIGGER [dbo].[trg_dbo_PBITree_TreeModel_HierarchicalEscalationHistory_InsertNode] 
  ON [dbo].[Table_1]
  AFTER UPDATE 
  AS
  BEGIN
      -- NodeModelInsertOrUpdateTPH
      IF ((select [Item] from inserted) = 'X')
          BEGIN
              UPDATE tx
              SET 
                  tx.LastUpdatedBy = i.LastUpdatedBy,
                  tx.LastUpdatedAt = i.LastUpdatedAt
              FROM 
                  [dbo].[Table_X] tx,
                  inserted i
              WHERE
                  tx.OtherItemId = i.OtherItemId
          END
  END
  </code></pre>
  
  <p>Anyone have any ideas?</p>