使用CTE更新记录？

本文介绍了使用CTE更新记录？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 ALTER PROCEDURE SP_PriceUpdate -- Add the parameters for the stored procedure here @PriceRuleID INT = NULL, @CategoryID INT = NULL, @SiteID INT = NULL AS SET NOCOUNT ON; BEGIN WITH PriceCTE(ProductID, CategoryID, SalePrice) AS ( SELECT ProductID, CategoryID, SalePrice FROM CAT_Products WHERE CategoryID = @CategoryID ) SELECT ProductID,categoryID,SalePrice FROM PriceCTE DECLARE @Value DECIMAL(32,2), @Type NVARCHAR(5), @Inc_Dec NVARCHAR(5) SELECT @Value = value FROM VB_PriceRule WHERE ID = @PriceRuleID AND SiteId = @SiteID SELECT @Type = [type] FROM VB_PriceRule WHERE ID = @PriceRuleID AND SiteId = @SiteID SELECT @Inc_Dec = Inc_Dec FROM VB_PriceRule WHERE ID = @PriceRuleID AND SiteId = @SiteID IF(@Type = '+') BEGIN IF(@Inc_Dec = '%') BEGIN --print 'MSG' UPDATE CAT_Products SET SalePrice = SalePrice + (@Value/100*SalePrice) FROM PriceCTE WHERE CategoryID = @CategoryID END ELSE BEGIN UPDATE CAT_Products SET SalePrice = SalePrice + @Value FROM PriceCTE WHERE CategoryID = @CategoryID END END ELSE BEGIN IF(@Inc_Dec = '%') BEGIN UPDATE CAT_Products SET SalePrice = SalePrice - (@Value/100*SalePrice) FROM PriceCTE WHERE CategoryID = @CategoryID END ELSE BEGIN UPDATE CAT_Products SET SalePrice = SalePrice - @Value FROM PriceCTE WHERE CategoryID = @CategoryID END END END

Q）这是我的查询...我想要更新CAT_Products的销售价格。根据使用CTE的条件。但这给我的结果是无效的对象名称PriceCTE

Q) Here is my query...I want to update the sales price of CAT_Products. As per the conditions using CTE. But it gives me the results as "Invalid object name PriceCTE"

推荐答案

CTE在单个语句的范围内有效因此，由于 PriceCTE 是在 SELECT…FROM PriceCTE 语句的范围内定义的，因此不能

A CTE is valid within the scope of the single statement it is defined in. Therefore, since PriceCTE is defined within the scope of the SELECT … FROM PriceCTE statement, it cannot be accessed in your UPDATEs.

以下解决方案将CTE定义与UPDATE结合在一起。

The following solution combines the CTE definition with UPDATE. It also combines all your updates in one statement.

ALTER PROCEDURE SP_PriceUpdate -- Add the parameters for the stored procedure here @PriceRuleID INT = NULL, @CategoryID INT = NULL, @SiteID INT = NULL AS SET NOCOUNT ON; BEGIN DECLARE @Value DECIMAL(32,2), @Type NVARCHAR(5), @Inc_Dec NVARCHAR(5); SELECT @Value = value, @Type = [type], @Inc_Dec = Inc_Dec FROM VB_PriceRule WHERE ID = @PriceRuleID AND SiteId = @SiteID; WITH PriceCTE(ProductID, CategoryID, SalePrice) AS ( SELECT ProductID, CategoryID, SalePrice FROM CAT_Products WHERE CategoryID = @CategoryID ) UPDATE PriceCTE SET SalePrice = SalePrice + @Value * ( CASE @Type WHEN '+' THEN 1 ELSE -1 END * CASE @Inc_Dec WHEN '%' THEN SalePrice / 100 ELSE 1 END ) END

我没有更改变量名称，但是，正如我在对您的问题的评论中所说，两个变量 @Inc_Dec 和 @Type ，看起来好像他们需要交易位置。我只是根据他们的名字来判断，尤其是根据 @Inc_Dec 的名字来判断：那个名字更可能表示 + 或-，而不是％或相反的值。但这只是一个假设，自然，您必须更好地了解自己的东西。

I didn't change variable names, but, as I said in a comment to your question, two variables, @Inc_Dec and @Type, do look as if they need to trade places. I'm merely judging by their names, in particular, by the @Inc_Dec name: that one is more likely to mean + or -, rather than % or whatever is the opposite value. But that's just an assumption, and you must know your stuff better, naturally.