将执行 UPDATE 的 CURSOR 转换为 CTE

2021-09-10 00:00:00 tsql sql-server

我熟悉 CURSOR，但没有 CTE 的经验，也从未真正使用过它们，但我被告知它们是比 CURSOR 更好的选择

I am familiar with CURSORS, but have no experience of CTE's and have never really used them but have been advised that they are a better option than CURSOR's

为了尝试理解 CTE 的逻辑，我编写了以下 CURSOR，我希望将其转换为 CTE.有人可以帮我吗?

To try and understand the logic of CTE's, I have written the following CURSOR which I hope to convert to a CTE. Can somebody help me?

DECLARE @CustomerID UNIQUEIDENTIFIER

DECLARE SalesOrderCursor CURSOR READ_ONLY
FOR
SELECT CustomerID FROM OrdersProcessed

OPEN SalesOrderCursor

FETCH NEXT FROM SalesOrderCursor INTO @CustomerID
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE CustomerOrderStatus SET Shipped = 1 WHERE CustomerID = @CustomerID

    END

    FETCH NEXT FROM SalesOrderCursor INTO @CustomerID 
END

推荐答案

我会用下面的命令

UPDATE CustomerOrderStatus 
SET Shipped = 1 
WHERE CustomerID IN
(
    SELECT CustomerID FROM OrdersProcessed
)

相关文章