CODEWITHSUNDEEP
sqlsql-servert-sql
Liberace
Liberace

Reputation: 81

SQL loop executes but new old values are over written

As my question title says, my program loops but all of my values I updated are being overwritten. Here's the code posted below. Say minRownum is 1 and max is 12, I see the loop execute 12 times correctly and min gets updated +1 each time. But in the end result, only the final row in my column whose RowNum is 12 have any values

I'm not exactly sure why overwriting is occurring since I'm saying "Update it where the rownumber = minrownumber" then I increment minrownum.

Can anyone point to what I am doing wrong? Thanks

 WHILE (@MinRownum <= @MaxRownum)
    BEGIN
        print ' here' 
        UPDATE #usp_sec
        set amount=(
        SELECT sum(amount) as amount   
        FROM dbo.coverage
        inner join dbo.owner
        on coverage.api=owner.api
        where RowNum=@MinRownum
        );

        SET @MinRownum = @MinRownum + 1

    END

PS: I edited this line to say (below) and now every value has the same wrong number (its not distinct but duplicated to all. 

        set amount = (SELECT sum(amount) as amount   
                      FROM dbo.coverage
                      INNER JOIN dbo.owner ON coverage.api = owner.api
where RowNum=@MinRownum
                     ) WHERE RowNum = @MinRownum;

Tables:

CREATE TABLE dbo. #usp_sec
(
RowNum int,
amount numeric(20,2),
discount numeric(3,2)
)

CREATE TABLE [dbo].[handler](
    [recordid] [int] IDENTITY(1,1) NOT NULL,
    [covid] [varchar](25) NULL,
    [ownerid] [char](10) NULL
)   

CREATE TABLE [dbo].[coverage](
    [covid] [varchar](25) NULL,
    [api] [char](12) NULL,
    [owncovid] [numeric](12, 0) NULL,
    [amount] [numeric](14, 2) NULL,
    [knote] [char](10) NULL
)

CREATE TABLE [dbo].[owner](
    [api] [char](12) NOT NULL,
    [owncovid] [numeric](12, 0) NULL,
    [ownerid] [char](10) NOT NULL,
    [officer] [char](20) NOT NULL,
    [appldate] [date] NOT NULL
    )

Upvotes: 0

Views: 75

Answers (1)

RBarryYoung
RBarryYoung

Reputation: 56745

Your UPDATE statement needs its own WHERE clause. Otherwise, each UPDATE will update every row in the table.

And the way you have this written, your subquery still needs its WHERE clause too. In fact, you need to definitively correlate the subquery to your table's (#usp_sec) rows. We cannot tell you how that should be done without more information such as your table definitions.

Upvotes: 3

Related Questions