Best SQL method to update a column based on date ranges from another column.

Question

My code is below. I am having a hard time as I am new to SQL to figure out what method would be simpler to use in order to update a column based on differential of dates. Basically what i want to do is if the date is in between today to today minus 7 days (update the symbology_bbl column to week1). the following has been updated.

USE [databasename]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE or Alter PROCEDURE gisuser.GetTheDate
AS
BEGIN 
SET NOCOUNT ON;  
    Declare @rowcount int
    Declare @editedDate datetime
    Declare @Symbology_BBL nvarchar(25)

    Declare mycursor cursor FORWARD_ONLY READ_ONLY LOCAL FOR 
        select objectID, Edited_Date, Symbology_BBL 
        from [tablename] order by objectid asc
    Open mycursor
    fetch next from mycursor
        into @rowcount, @editedDate, @Symbology_BBL

    while @@FETCH_STATUS = 0
    Begin
    --if edited_date is from 11/21/2018 to 11/28/2018
    begin
    set @Symbology_BBL = 'Week1'
    end
    --elseif edited_date is from 11/15/2018 to 11/20/2018
    begin
    set @Symbology_BBL = 'Week2'
    end
    else 
    begin
    set @Symbology_BBL = 'Greater than Week3'
    end
--*******************************************************************************
        Update [tablename]
        set symbology_bbl = @Symbology_BBL

        fetch next from mycursor
        into @rowcount, @editedDate, @Symbology_BBL
      End
    Close mycursor
    deallocate mycursor
END

Thanks for the help in advance.

I don't see anything missing in your code. Probably it's not an infinite loop, it's just running longer than you expect. — Tab Alleman, Nov 28 '18 at 21:01
Looping for updates is the spawn of the devil. This can and should be done with a single update statement. And of course you don't have a where clause so every iteration here is updating the **entire table** each and every pass. And always to the same value. — Sean Lange, Nov 28 '18 at 21:18
SQL is a set based language. You are not supposed loop every row to update. Do tell what you're trying to do. — Eric, Nov 28 '18 at 21:42

score 3 · Accepted Answer · edited Nov 29 '18 at 15:20

3

Your query is just running slow and here's why.

Cursors perform terribly and this is RBAR methodology

Here is what your cursor is currently doing, and why it's taking a long time (aside from blocking from locks that are needed for updates, indexes, yada yada).

select count(Symbology_BBL) from tableName

What ever number is returned here... your cursor is

executing / looping this many times and
setting Symbology_BBL = 'usa' for every single row... every single time

So basically, if there were 1000 rows in that table, you are doing an update on every row, 1000 times. And this, makes zero sense what so ever or at least is about the least performant way you could structure your update. What you most likely want is an UPDATE with a JOIN but you haven't provided enough to determine that.

Also, you could get a slight boost using FAST_FORWARD instead of READ_ONLY FORWARD_ONLY which you should at least have added LOCAL STATIC to, since cursors are global by default (and unnecessary in your use case). But Erik Darling shows how this could prevent it from going parallel... sneaky Microsoft... and thus FORWARD_ONLY with LOCAL STATIC could be faster... again add that LOCAL STATIC for most cursors.

EDIT

Based on your comment and edit here is the simplest method...

update tablename
set Symbology_BBL = case 
                    when last_edited_date between GETUTCDATE() -7  and GETUTCDATE()
                    then 'Week 1'
                    when last_edited_date between GETUTCDATE() - 14 and GETUTCDATE() - 8
                    then 'Week 2'
                    else 'Greater Than Week 3'
                    end

edited Nov 29 '18 at 15:20

JT4U

620
4
18

answered Nov 28 '18 at 21:08

S3S

24,809
5
26
45

how would i be able to do that join i am just using one table to get the two columns from. – JT4U Nov 28 '18 at 21:13
1

Considering the logic you've provided us, @JT4U, the statement `UPDATE [Tablename] SET symbology_bbl = 'usa';` would achieve the exact same final result. – Thom A Nov 28 '18 at 21:15
1

if you add some sample data and expected results we can show you. Otherwise it'd be a guess. – S3S Nov 28 '18 at 21:15
i need the procedure to run and loop through each and every record to basically do this in it. i have updated the procedure code in the question above.so basically i need all symbology_bbl to change from 'america' to 'usa'. this is just an example. not actual code like a scenario. – JT4U Nov 28 '18 at 21:37
Then use this... `Update [tablename] set symbology_bbl = 'usa' where symbology_bbl = 'America'`. That's all... no cursor, no looping. – S3S Nov 28 '18 at 21:47
okay so lets say i have another column its called edited date. i need to say if edited_date is between today and 11/20/2018 update symbology_bbl to 'usa', else if the row in edited_date is between 11/13/2018 to 11/19/2018 update symbology to 'canada'. something like that. thats why i need a loop to see each row and what date has been assigned to edited_date – JT4U Nov 28 '18 at 21:53
You don't need to loop, i can assure you, but if you open a new question with sample data and expected output, or edit this one--either way, we can work with you. You are throwing a lot of hypotheticals and we need a [MCVE](https://stackoverflow.com/help/mcve) – S3S Nov 28 '18 at 21:56
i just edited the question code with -- what i need and stuff – JT4U Nov 28 '18 at 22:01
1

Done, without a loop. see the edit. In the future, be sure to add all of this in the beginning so you don't run into the XY Problem: http://xyproblem.info/ – S3S Nov 28 '18 at 22:04
Thank you so much for the help @scsimon this is exactly what i needed. i marked this as an answer. I fixed my question but if you feel like it can be better updated please go ahead and fix the question that i asked so future new sql users can better understand this scenario as i am a java/c# programmer and i was using loops. – JT4U Nov 29 '18 at 15:19

score 0 · Answer 2 · answered Nov 28 '18 at 20:59

0

This update has no where clause so it will write to all rows.

    Update [tablename]
    set symbology_bbl = @Symbology_BBL

answered Nov 28 '18 at 20:59

Kevin Panko

8,356
19
50
61

1

that is just something i tried to see if the procedure is working however, i need to do some if statements inside the code. even with just that update statement its taking forever. like an infinite loop. – JT4U Nov 28 '18 at 21:04

Best SQL method to update a column based on date ranges from another column.

2 Answers2