SQL Server 2019: how to to Update Multiple Rows with a CTE Using a WHERE Clause
I'm deploying to production and Quick question that's been bugging me - I've been banging my head against this for hours. I'm working with an scenario when trying to update multiple rows in a SQL Server 2019 database using a Common Table Expression (CTE). I want to update the `Status` column of my `Orders` table based on specific conditions, but the query isn't updating any rows, and I'm not sure why. My query looks like this: ```sql WITH OrderCTE AS ( SELECT OrderID, Status FROM Orders WHERE OrderDate < '2023-01-01' AND Status = 'Pending' ) UPDATE OrderCTE SET Status = 'Completed' WHERE Status = 'Pending'; ``` After running this query, I expected that all `Pending` orders before 2023 would be updated to `Completed`. However, no rows are affected, and I get the message: "(0 rows affected)." I've double-checked the `Orders` table, and there are indeed records that meet the criteria. To troubleshoot, I executed the CTE by itself, and it returns the expected rows: ```sql WITH OrderCTE AS ( SELECT OrderID, Status FROM Orders WHERE OrderDate < '2023-01-01' AND Status = 'Pending' ) SELECT * FROM OrderCTE; ``` This returns the correct `OrderID`s and `Status`. I've also tried simplifying the update statement and removing the `WHERE` clause entirely, which works, but I really want to keep that condition. I'm considering if there's something specific about using a CTE in this context that I'm missing. Is there a limitation or best practice that I should be aware of when updating rows through a CTE in SQL Server? Any help would be greatly appreciated! This is part of a larger API I'm building. My development environment is Linux. I'd really appreciate any guidance on this. Any help would be greatly appreciated!