在SQL存储过程中使用select和join in where子句进行批量更新

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

我在SQL Server 2012中有两个表，具有以下结构：

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

Here is the SELECT statement I am using:

这是我正在使用的SELECT语句：

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语句：

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?

我也尝试将select语句放在UPDATE语句的WHERE子句中，但这不起作用。我看不出我错过了什么，有人可以帮助我吗？

1 个解决方案

#1

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:

在SQL Server中，您可以将表别名指定为更新目标。您不必为列名添加前缀，因为您只能使用单个更新来更新一个表：

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.

不需要在WHERE子句中重复ON子句，这已经被处理掉了。

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

请注意，在SELECT查询中，您加入了成员资格表，但您的更新加入了MembershipPurchaseHistory表。这可能会产生不同的结果。

#1