CODEWITHSUNDEEP
sqlsql-serverjoininner-join
Nick
Nick

Reputation: 3845

SQL server join issue

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.

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.

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

cheers

Upvotes: 2

Views: 58

Answers (1)

Devart
Devart

Reputation: 122032

Try this one -

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

Small check - 

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

Upvotes: 2

Related Questions