How to update the column without loop in SQL Server?

Question

Due to performance perspective I just need to remove loop and using some joins or other solution to update the data in #Result table and get the same result which return by loop.

Scalar function:

CREATE FUNCTION [MultiplyerScl]
    (@a INT, @b INT)
RETURNS INT
AS
BEGIN
    DECLARE @i AS INT = 2
    DECLARE @Value AS INT 

    IF (@b % @a = 0) 
    BEGIN
        SET @Value = @b
    END
    ELSE 
    BEGIN
        WHILE (1=1) 
        BEGIN
            IF((@b * @i) % @a = 0) 
            BEGIN
                SET @Value = @b * @i
                BREAK;
            END
            ELSE 
            BEGIN
                SET @i = @i + 1
            END
        END
    END

    RETURN @Value
END

Table design and its value.

CREATE TABLE #NUM (Groupid INT, GroupValue INT)

INSERT INTO #NUM 
VALUES (1, 8), (1, 9), (1, 23), (2, 5), (2, 5), (2, 10)

Main for loop logic.

SELECT 
    Groupid,
    GroupValue,
    MaxValue = MAX(GroupValue) OVER (PARTITION BY Groupid),
    MaxCount = COUNT(1) OVER(),
    RID = ROW_NUMBER() OVER (ORDER BY groupid)
INTO 
    #Result
FROM 
    #NUM 

DECLARE @i AS INT = 1
DECLARE @RawCnt AS INT = (SELECT MAX(MaxCount) FROM #Result)
DECLARE @iGroupid INT
DECLARE @iGroupvalue INT
DECLARE @iMaxValue INT

WHILE(@i <= @RawCnt) 
BEGIN
    SELECT 
        @iGroupid = Groupid,
        @iGroupvalue = Groupvalue,
        @iMaxValue = MaxValue 
    FROM 
        #Result 
    WHERE 
        RID = @i

    UPDATE #Result
    SET MaxValue = dbo.[MultiplyerScl](@iGroupvalue, @iMaxValue)
    WHERE Groupid = @iGroupid

    SET @i = @i + 1
END

SELECT * FROM #Result

You can make this question better if you write, in your own words, what your code should do, and even much better if you provided desired results to go with your sample data. Also, you should remove the irrelevant version tags, leaving only the version you actually need this to run on. If you need multiple versions, only state the oldest one. — Zohar Peled, Sep 13 '18 at 20:49
Here I need performance in query.So I kept prerequisite code so anyone can use directly this code and try to make the solution. No need to west time in set up the data and table all that. Also next time i will taken care for minimal tags. — NP007, Sep 13 '18 at 21:02
If you don't want to invest the time it takes to ask a good question, for solving your own problem, how do you expect strangers to invest their time to answer your question, and get nothing in return? — Zohar Peled, Sep 13 '18 at 21:05
@Zohar You just need to take my code and run in SSMS then you will get result. And I need same result without that loop. That is my question. Also Let me know what information you need from me. I need something like this instead of loop. https://stackoverflow.com/questions/12583043/t-sql-update-table-columns-using-function — NP007, Sep 14 '18 at 04:21

Naim Halai · Accepted Answer · 2018-09-17T12:44:42.317

Try this out

SELECT 
Groupid,
GroupValue,
MaxValue = MAX(GroupValue) OVER (PARTITION BY Groupid),
MaxCount = COUNT(1) OVER(),
RID = ROW_NUMBER() OVER (ORDER BY groupid)
INTO 
#Result
FROM 
#NUM

;WITH Res AS 
( 
SELECT Groupid, e.Groupvalue, dbo.[MultiplyerScl](Groupvalue, e.MaxValue) AS 
MaxValue, 1 AS i
FROM #Result e
UNION ALL 
--recursive execution 
SELECT e.Groupid, m.Groupvalue, dbo.[MultiplyerScl](e.Groupvalue, m.MaxValue) AS MaxValue, m.i + 1 AS i
FROM #Result e
INNER JOIN Res m ON e.Groupid = m.Groupid
WHERE dbo.[MultiplyerScl](e.Groupvalue, m.MaxValue) > m.MaxValue
)

SELECT Groupid, MAX(MaxValue) AS MaxValue
INTO #FinalResult
FROM Res
GROUP BY Groupid

UPDATE re
SET re.MaxValue = ire.MaxValue
FROM #FinalResult ire
INNER JOIN #Result re ON re.Groupid = ire.Groupid

SELECT * FROM #Result

How to update the column without loop in SQL Server?

1 Answers1

Linked