update table b if b.time falls between any of table a rows start/stop

  • delizat4g

    Old Hand

    Points: 364

    I'm trying to find a way to rewrite this dusty old cursor set based, if possible.

    It's updating a column in table b if b.time falls between any of table a rows start/stop times.

    Thanks, D

    DECLARE op_cursor CURSOR LOCAL FORWARD ONLY
    FOR SELECT "start_dtg", "end_dtg"
    FROM "tableA"
    OPEN op_cursor
    FETCH NEXT FROM op_cursor INTO @SDTG, @EDTG
    WHILE @@FETCH_STATUS = 0
    BEGIN
    UPDATE @tableB SET "invalid" = 0
    WHERE "time" BETWEEN @SDTG AND @EDTG
    FETCH NEXT FROM op_cursor INTO @SDTG, @EDTG
    END
    CLOSE op_cursor
    DEALLOCATE op_cursor

     

    Code-Blooded

  • pietlinden

    SSC Guru

    Points: 62455

    Do you have some sample data?

    CREATE TABLE scripts, INSERT scripts, please.

  • DesNorton

    SSC-Insane

    Points: 22700

    Without sample data, this is untested code.  That said, it appears to be a very simple update

    UPDATE b
    SET [invalid] = 0
    FROM tableB AS b
    INNER JOIN tableA AS a
    ON b.[time] BETWEEN a.[start_dtg] AND a.[end_dtg]
  • ScottPletcher

    SSC Guru

    Points: 98119

    You don't really need to join, an EXISTS check will do fine.  The optimizer may rewrite it that way anyway, but it doesn't hurt to code it that way yourself:

    UPDATE B
    SET "invalid" = 0
    FROM @tableB B
    WHERE EXISTS (
    SELECT 1
    FROM tableA A
    WHERE B."time" BETWEEN A."start_dtg" AND A."end_dtg"
    )

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • delizat4g

    Old Hand

    Points: 364

    Both of these are great. This exist might work for me.  I now need to add a third table like...

    Working on trying out the WHERE EXISITS @scottpletcher

    Yes....I know the queries seem hoaxy, but I am limited. Thanks

    DECLARE op_cursor CURSOR LOCAL FORWARD ONLY
    FOR SELECT b."start_dtg", b."end_dtg"
    FROM "tableA" a
    LEFT JOIN tableB b ON a._Id = b._tableA_Id
    WHERE a._id = 3
    AND a.color = 'blue'
    GROUP BY b."start_dtg", b."end_dtg"
    OPEN op_cursor
    FETCH NEXT FROM op_cursor INTO @SDTG, @EDTG
    WHILE @@FETCH_STATUS = 0
    BEGIN
    UPDATE @tempTableB
    SET "invalid" = 0
    WHERE "time" BETWEEN @SDTG AND @EDTG
    FETCH NEXT FROM op_cursor INTO @SDTG, @EDTG
    END
    CLOSE op_cursor
    DEALLOCATE op_cursor

    Code-Blooded

  • drew.allen

    SSC Guru

    Points: 76588

    STOP USING CURSORS for simple updates.  A previous response showed how to rewrite your original query without using a cursor.

    SQL Server is optimized for SET-BASED operations.  Using a CURSOR prevents SQL Server from using those optimizations.  In my 20 year career with SQL Server, I have NEVER had to write a CURSOR.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • delizat4g

    Old Hand

    Points: 364

    @drew_allen your response is not helpful in the least.

    1. If you had bothered to read my original post I've already stated that I

    "I'm trying to find a way to rewrite this dusty old cursor set based, if possible."

    2. There are very good reasons why someone would use a cursor. Just not in this case.

     

    Code-Blooded

  • DesNorton

    SSC-Insane

    Points: 22700

    Again, this code is untested, as there is no sample structure or data to work with.

    Using a Join

    WITH cteBase AS (
    SELECT b."start_dtg", b."end_dtg"
    FROM "tableA" a
    LEFT JOIN tableB b ON a._Id = b._tableA_Id
    WHERE a._id = 3
    AND a.color = 'blue'
    GROUP BY b."start_dtg", b."end_dtg"
    )
    UPDATE tB
    SET "invalid" = 0
    FROM @tempTableB tB
    INNER JOIN cteBase cte
    ON tB."time" BETWEEN cte."start_dtg" AND cte."end_dtg";

    Using EXISTS

    UPDATE tB
    SET "invalid" = 0
    FROM @tempTableB tB
    WHERE EXISTS (
    SELECT 1
    FROM "tableA" a
    LEFT JOIN tableB b ON a._Id = b._tableA_Id
    WHERE a._id = 3
    AND a.color = 'blue'
    WHERE tB."time" BETWEEN b."start_dtg" AND b."end_dtg"
    );
  • delizat4g

    Old Hand

    Points: 364

    Awesome! I think this will work. I'm gonna test it out now.

    Thanks!!

    Code-Blooded

  • delizat4g

    Old Hand

    Points: 364

    @desnorton Thanks very much for the help! Using EXISTS ended up being a few seconds faster and I haven't seen it used this way soo also nice.

     

    Code-Blooded

  • DesNorton

    SSC-Insane

    Points: 22700

    delizat4g wrote:

    @desnorton Thanks very much for the help! Using EXISTS ended up being a few seconds faster and I haven't seen it used this way soo also nice.

    The EXISTS was actually @scottpletcher's code that I modified for your scenario.

     

    That said, changing the LEFT JOIN in the following code to an INNER JOIN might also have an additional perf improvement.  My summation is based on the premise that you can't compare BETWEEN NULL AND NULL

    Change

    LEFT JOIN tableB b ON a._Id = b._tableA_Id

    to

    INNER JOIN tableB b ON a._Id = b._tableA_Id

     

  • delizat4g

    Old Hand

    Points: 364

    I def want to thank @scottpletcher's for the help also!

    It's difficult and I'm sure annoying because I have to create "dummy" queries, but in this case the two tables are parent/child so it will always find a match.

     

    Code-Blooded

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply