CODEWITHSUNDEEP
sql-servercursor
Bashir
Bashir

Reputation: 61

Why does this cursor insert the same values into each row?

This cursor is supposed to select the year a company was founded in then subtract that from the current year and insert the result into a row called years running. It works partially, however it puts the result of the first calculation into every row and then moves onto the second row and puts the result of the second calculation into every row. How do I put the results of the first into the first row then the second into the second and so on.

  --Declaring variables for the cursor, I will need to pull out the year the company was founded in 
    --from the table.

    DECLARE @Yearfounded int
    --Now I am going to start work on the cursor

    --Declares my cursor

    DECLARE @YearsCursor CURSOR

    -- sets the cursor to select the column I want

    SET @YearsCursor = CURSOR FOR SELECT  [YEar] From dbo.YearsRunning

    -- Opens the cursor
    OPEN @YearsCursor

    -- selects the next value from years column and puts it into variable

    FETCH NEXT FROM @YearsCursor into @Yearfounded

    -- while there are rows
    WHILE @@FETCH_STATUS = 0

    -- starts the loop
    Begin

    -- declaring variables that are used 
    DECLARE @CurrentYear int = year(getdate())
    DECLARE @YearsRunning int

    Update dbo.YearsRunning SET YearsRunning  =  @CurrentYear - @Yearfounded

    print @YearsRunning




    Fetch Next From @YearsCursor into @Yearfounded

    --UPDATE dbo.YearsRunning SET YearsRunning = @YearsRunning
    -- fetches the next year
    End

    Close @YearsCursor
    Deallocate @YearsCursor

Upvotes: 1

Views: 280

Answers (2)

Denis Rubashkin
Denis Rubashkin

Reputation: 2191

Each step of your cursor updates all rows in your table because you don't have any conditions in your UPDATE statement.

Upd. To update each row one by one you should modify your query for getting UPDATEstatement like:

Update dbo.YearsRunning 
SET YearsRunning  =  @CurrentYear - @Yearfounded
WHERE id = @Id

id in this case is your unique key. But updating in this way is a bad practice. You'd better work with data sets but not with separate rows. For example:

UPDATE dbo.YearsRunning 
SET YearsRunning = YEAR(GETDATE()) - [YEar]

Upvotes: 0

Stanislav Kundii
Stanislav Kundii

Reputation: 2894

Why do you need a cursor?

Update dbo.YearsRunning SET YearsRunning = year(getdate()) - YEar

Upvotes: 1

Related Questions