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

Question

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?

Answer 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

Answer 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;