T-SQL Update with Max and Min value from another table

All we need is an easy explanation of the problem, so here it is.

I don’t have much experience with SQL queries and I’m trying to learn and understand from my mistakes.
I have two tables:

CREATE TABLE #TempTest
(Id INT,
Number INT)

CREATE TABLE #TempTest2
(Id INT,
MaxNo INT,
MinNo INT)

INSERT INTO #TempTest VALUES (1,4)
INSERT INTO #TempTest VALUES (1,6)
INSERT INTO #TempTest VALUES (1,9)
INSERT INTO #TempTest VALUES (1,7)
INSERT INTO #TempTest VALUES (1,3)
INSERT INTO #TempTest VALUES (1,1)
INSERT INTO #TempTest VALUES (1,5)

INSERT INTO #TempTest2 VALUES (1,0,10)

And I’m trying to update the columm MaxNo with the max number smaller than 8 from the first table and MinNo with the minimum bigger than 1:

UPDATE t2
SET MaxNo = IIF(t1.Number>t2.MaxNo AND t1.Number<8,t1.Number, t2.MaxNo),
MinNo = IIF(t1.Number<t2.MinNo AND t1.Number>1,t1.Number, t2.MinNo)
FROM #TempTest2 t2
JOIN #TempTest t1 ON t2.Id=t1.Id

But it updated both columns with value 4. I’ve also tried using

BEGIN Transaction
...
COMMIT

around the update, but the result is the same.

What am I doing wrong?
What is the best approach to update the second table correctly?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

For MinNo with the minimum bigger than 1 = will ignor(mark as null) all the records smaller or equal to 1.

 CASE WHEN Number <=1 THEN NULL ELSE Number END

For max number smaller than 8 = will ignor(mark as null) all the records greater or equal to 8.

 CASE WHEN Number >= 8 THEN NULL ELSE Number END

The query will look something like this:

UPDATE t2
    SET MaxNo =  t1.MaxNo,
        MinNo = t1.MinNo
FROM 
    #TempTest2 as t2
    INNER JOIN
    (
        SELECT id, 
                MinNo = MIN(CASE WHEN Number <=1 THEN NULL ELSE Number END),
                MaxNo = MAX(CASE WHEN Number >= 8 THEN NULL ELSE Number END)
        FROM 
            #TempTest
        GROUP BY 
            id
    )as t1
    ON t2.Id = t1.Id;

SELECT *
FROM #TempTest2;

output:

Id MaxNo MinNo
1 7 3

dbfiddle

Method 2

One way is you can use a CTE to temporarily stage the result set of your min and max values, then CROSS JOIN it to your #TempTest2 table so you can use it in an UPDATE statement like so:

WITH _MinAndMax AS
(
    SELECT 
        MIN(Number) AS NumberMin,
        Max(Number) AS NumberMax
    FROM #TempTest
    WHERE Number > 1 -- Filters out anything less than or equal to 1
        AND Number < 8 -- Filters out anything greater than or equal to 8
)

UPDATE TT2
SET TT2.MaxNo = MAM.NumberMax,
    TT2.MinNo = MAM.NumberMin
FROM #TempTest2 AS TT2
CROSS JOIN _MinAndMax AS MAM;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply