Bulk update in SQL stored procedure with select and join in where clause

Question

I have two tables in SQL Server 2012 with the following structure:

customer  
- internalId  
- listingStatus  
- inactive  
membership  
- custIntneralId  
- regDate

Here is the SELECT statement I am using:

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 = ''

And here is the UPDATE statement if I were to use a loop to insert row by row:

UPDATE customer SET listingStatus = '6' WHERE internalId=custInternalId

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:

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 = ''

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?

Andomar · Accepted Answer · 2017-08-11T21:54:02.400

In SQL Server you can specify the table alias as the update target. You don't have to prefix column names, since you can only update one table with a single update:

UPDATE  t1
SET     custentity_iahp_listing_status = '6'  
FROM    customer AS t1
JOIN    MembershipPurchaseHistory AS t2
ON      t1.internalId = t2.custInternalId  
WHERE   t1.inactive = 0
        AND t1.custentity_iahp_listing_status = ''

No need to repeat the ON clause in the WHERE clause, that's been taken care of.

Note that in your SELECT query you join the membership table, but your update joins the MembershipPurchaseHistory table. That might give different results.

Bulk update in SQL stored procedure with select and join in where clause

1 Answers1