使用CTE更新记录？ [英] Updating record using 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

我没有更改变量名称，但是，正如我在对您的问题的评论中所说，两个变量< c $ c> @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.

这篇关于使用CTE更新记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！