title: Bulk update in SQL stored procedure with select and join in where clause
body: <p>I have two tables in SQL Server 2012 with the following structure:  </p>
  
  <pre><code>customer  
  - internalId  
  - listingStatus  
  - inactive  
  membership  
  - custIntneralId  
  - regDate
  </code></pre>
  
  <p>Here is the SELECT statement I am using:  </p>
  
  <pre><code>USE [dbOne]  
  SELECT DISTINCT(t1.internalId)  
  FROM customer AS t1  
  INNER JOIN membership AS t2 ON t1.internalId = t2.custInternalId  
  WHERE t1.internalId = t2.custInternalId  
  AND t1.inactive = 0  
  AND t1.listingStatus = ''  
  </code></pre>
  
  <p>And here is the UPDATE statement if I were to use a loop to insert row by row:  </p>
  
  <pre><code>UPDATE customer SET listingStatus = '6' WHERE internalId=custInternalId   
  </code></pre>
  
  <p>Since I want to do bulk updates rather than row by row, I am trying to write a stored procedure I can trigger to do a bulk update. I have tried several different ways to write it, but have been unable to get them to work. Here is an example of one of the stored procedures I have tried:  </p>
  
  <pre><code>USE [dbOne]  
  UPDATE customer  
  SET customer.listingStatus = '6'  
  FROM customer AS t1  
  INNER JOIN membership AS t2 ON t1.internalId = t2.custInternalId  
  WHERE t1.internalId = t2.custInternalId  
  AND t1.inactive = 0  
  AND t1.listingStatus = ''  
  </code></pre>
  
  <p>I've also tried putting the select statement inside the WHERE clause of the UPDATE statement, that didn't work. I can't see what I'm missing, can anyone help me?</p>