SQL Server连接问题 [英] SQL server join issue

问题描述

我在返回奇怪结果的存储过程中具有以下语句.给定两列，其中一列(RL)小于0，例如-2，则应将2加到另一列(HD).如果负值为-8，则应在HD列中加上8.

i have the following statement in a stored procedure that is returning strange results. Given two columns where one (RL) is less than 0 e.g -2, it should add 2 to another column (HD). If the negative value was -8 it should add 8 to the HD column.

在刚刚完成的测试中，RL列为0，HD为2.我将RL更改为-2并运行代码.我正在执行:RL = 0且HD =4.INSTEAD 结果是RL = 0且HD = 5.

In a test ive just done, the RL column had 0 and HD was 2. I changed the RL to -2 and run the code. I was EXPECTING : RL = 0 and HD = 4. INSTEAD the RESULT was RL = 0 and HD = 5.

我认为问题是由于存在联接.我该怎么写，用WHERE子句替换join.

I think the problem is due to the presence of the join. How would i write this to replace the join with a WHERE clause please.

UPDATE P
SET P.HD = P.HD + P.RL
    ,P.RL = 0
FROM Products P
INNER JOIN (
    SELECT id
        ,RL
    FROM Products
    WHERE id IN (
            SELECT ProductID
            FROM OrderDetails
            WHERE OrderID = @OrderId
            )
        AND RL < 0
    ) Q ON P.ID = Q.id

欢呼

推荐答案

尝试一下-

UPDATE Products
SET HD = HD + RL,
    RL = 0
FROM P
WHERE RL < 0
    AND ID IN (
            SELECT ProductID
            FROM dbo.OrderDetails
            WHERE OrderID = @OrderId
        )

小额支票-

DECLARE @t TABLE (a INT, b INT)

INSERT INTO @t (a, b)
VALUES (1, 2)

UPDATE @t
SET a = b, b = 0

SELECT * FROM @t

这篇关于SQL Server连接问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！