5

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?

2 Answers 2

3

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.