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

Question

Post reply

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

delizat4g

Old Hand

Points: 369

September 20, 2019 at 10:39 pm

Go to Answer

#3682118

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

DesNorton

SSC-Insane

Points: 24278

September 24, 2019 at 5:03 am

Answer

#3682849

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"
    );

How to post data/code on a forum to get the best help.

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

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

pietlinden SSC Guru Points: 63428 More actions · Answer 1

Do you have some sample data?

CREATE TABLE scripts, INSERT scripts, please.

DesNorton SSC-Insane Points: 24278 More actions · Answer 2

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]

How to post data/code on a forum to get the best help.

ScottPletcher SSC Guru Points: 100947 More actions · Answer 3

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) A socialist is someone who will give you the shirt off *someone else's* back.

delizat4g Old Hand Points: 369 More actions · Answer 4

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: 76944 More actions · Answer 5

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: 369 More actions · Answer 6

@drew_allen your response is not helpful in the least.

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

delizat4g Old Hand Points: 369 More actions · Answer 7

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

Thanks!!

Code-Blooded

delizat4g Old Hand Points: 369 More actions · Answer 8

@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: 24278 More actions · Answer 9

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

How to post data/code on a forum to get the best help.

delizat4g Old Hand Points: 369 More actions · Answer 10

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