better way to write this update

Question

Post reply

better way to write this update

tran008

Ten Centuries

Points: 1002
More actions
December 1, 2008 at 7:15 am

#127297

I have the following update statement that when thru the table and update the field, as you can see this is repeating table from 1 step to another...I know there is a better way to rewrite this. Any help is appreciates.
thanks
--------------------------
UPDATE TABLE1
SET CHK_DATE = 1
WHERE TRXID IN (SELECT A.TRXID
FROM TABLE1 A
LEFT JOIN TABLE2 B
ON DATEADD(DAY,DATEDIFF(DAY,0,A.TRXDATE),0) = DATEADD(DAY,DATEDIFF(DAY,0,B.MARKETDATE),0)
WHERE A.TRXDATE IS NULL)
UPDATE TABLE1
SET CHK_PRICE = 1
WHERE TRXID IN (SELECT A.TRXID
FROM TABLE1 A
INNER JOIN TABLE2 B
ON DATEADD(DAY,DATEDIFF(DAY,0,A.TRXDATE),0) = DATEADD(DAY,DATEDIFF(DAY,0,B.MARKETDATE),0)
WHERE A.TRXPRICE NOT BETWEEN B.STOCKLOW AND B.STOCKHIGH)
UPDATE TABLE1
SET CHK_TOTAL = 1
WHERE TRXID IN (SELECT A.TRXID
FROM TABLE1 A
INNER JOIN TABLE2 B
ON DATEADD(DAY,DATEDIFF(DAY,0,A.TRXDATE),0) = DATEADD(DAY,DATEDIFF(DAY,0,B.MARKETDATE),0)
WHERE ABS(A.TRNTOTAL) NOT BETWEEN (((A.QTY * (B.STOCKLOW)) - (A.QTY * (B.STOCKLOW)) * 0.01)) AND ((A.QTY * (B.STOCKHIGH)) + ((A.TRXQTY * (B.STOCKHIGH)) * 0.01)))

Viewing 4 posts - 1 through 3 (of 3 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Not sure if you can get all three updates into one statement, but you can certainly speed things up:

[font="Courier New"]UPDATE TABLE1

SET CHK_DATE = 1

WHERE TRXID IN (SELECT A.TRXID

FROM TABLE1 A

LEFT JOIN TABLE2 B

ON DATEADD(DAY,DATEDIFF(DAY,0,A.TRXDATE),0) = DATEADD(DAY,DATEDIFF(DAY,0,B.MARKETDATE),0)

WHERE A.TRXDATE IS NULL)

-- This is the same as...

UPDATE TABLE1 SET CHK_DATE = 1 WHERE TRXDATE IS NULL

----------------------------------------------------------------------

UPDATE TABLE1

SET CHK_PRICE = 1

WHERE TRXID IN (SELECT A.TRXID

FROM TABLE1 A

INNER JOIN TABLE2 B

ON DATEADD(DAY,DATEDIFF(DAY,0,A.TRXDATE),0) = DATEADD(DAY,DATEDIFF(DAY,0,B.MARKETDATE),0)

WHERE A.TRXPRICE NOT BETWEEN B.STOCKLOW AND B.STOCKHIGH)

-- and

UPDATE TABLE1

SET CHK_TOTAL = 1

WHERE TRXID IN (SELECT A.TRXID

FROM TABLE1 A

INNER JOIN TABLE2 B

ON DATEADD(DAY,DATEDIFF(DAY,0,A.TRXDATE),0) = DATEADD(DAY,DATEDIFF(DAY,0,B.MARKETDATE),0)

WHERE ABS(A.TRNTOTAL) NOT BETWEEN (((A.QTY * (B.STOCKLOW)) - (A.QTY * (B.STOCKLOW)) * 0.01)) AND ((A.QTY * (B.STOCKHIGH)) + ((A.TRXQTY * (B.STOCKHIGH)) * 0.01)))

-- could be run together as...

UPDATE A SET

CHK_TOTAL = CASE WHEN ABS(A.TRNTOTAL) NOT BETWEEN (((A.QTY * (B.STOCKLOW)) - (A.QTY * (B.STOCKLOW)) * 0.01))

AND ((A.QTY * (B.STOCKHIGH)) + ((A.TRXQTY * (B.STOCKHIGH)) * 0.01))) THEN 1 ELSE CHK_TOTAL END,

CHK_PRICE = CASE WHEN A.TRXPRICE NOT BETWEEN B.STOCKLOW AND B.STOCKHIGH THEN 1 ELSE CHK_PRICE END

FROM TABLE1 A

INNER JOIN TABLE2 B ON B.MARKETDATE >= DATEADD(DAY,DATEDIFF(DAY,0,A.TRXDATE),0)

AND B.MARKETDATE < DATEADD(DAY,DATEDIFF(DAY,0,A.TRXDATE),1)

----------------------------------------------------------------------

[/font]

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

Your join looks the same in all cases so this might work a little faster for you. I know it looks strange to have your criteria in both the case expressions and the where clause, but it will let you make your three updates in one pass. There may be some syntax errors, but you get the idea. Please let me know about performance if you test my simple join against your subquery syntax.

UPDATE TABLE1

SET

-- case expressions keep original values unless specific condition is met

CHK_DATE = case when a.trxDate is null then 1 else chk_date end

,CHK_PRICE = case when a.trxPrice not between b.StockLow and B.StockHigh then 1 else chk_price end

,CHK_TOTAL = case when (ABS(A.TRNTOTAL) NOT BETWEEN (((A.QTY * (B.STOCKLOW)) - (A.QTY * (B.STOCKLOW)) * 0.01)) AND ((A.QTY * (B.STOCKHIGH)) + ((A.TRXQTY * (B.STOCKHIGH)) * 0.01)) then 1 else chk_total end

FROM TABLE1 A

LEFT JOIN TABLE2 B ON DATEADD(DAY,DATEDIFF(DAY,0,A.TRXDATE),0) = DATEADD(DAY,DATEDIFF(DAY,0,B.MARKETDATE),0)

-- where clause stays to that only rows requiring changes to chk_date,chk_price, or chk_total are updated

WHERE A.TRXDATE IS NULL

or A.TRXPRICE NOT BETWEEN B.STOCKLOW AND B.STOCKHIGH

or (ABS(A.TRNTOTAL) NOT BETWEEN (((A.QTY * (B.STOCKLOW)) - (A.QTY * (B.STOCKLOW)) * 0.01)) AND ((A.QTY * (B.STOCKHIGH)) + ((A.TRXQTY * (B.STOCKHIGH)) * 0.01)))

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Much better... if you have a joined update, the object of the join MUST be in the FROM clause or you could end up pinning 4 cpu's to the wall for 2 hours on an update that should only take 6 seconds. It's a very rare occurance, but it will crush your server if and when it finally happens. It depends on how parallelism is formed, what indexes there are, and a thousand other things and that's what makes the problem so rare... but, do you feel lucky? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)