Solved

Need help with optimizing an Sql Update statement

Posted on 2006-11-29
3
419 Views
Last Modified: 2008-02-26
Basically I have three separate updates that I would like to combine into one.  Please note that the column MKT_RATE needs to be updated with the appropriate value based on the where clauses stated below. Can this be combined into one big update or am I stuck with three ?

        begin
           update order_fx set act_code = 'FXO_CCY', mkt_rate = @lst_rate

                where LTRIM(RTRIM(ord_stat)) in ('MISSED', '1', '2') AND
                        (mkt_rate <> @lst_rate and cur_1 = @cur_1 and cur_2 = @cur_2 and ord_type = 'CALL' and cancelled = 0 and      
                               executed = 0 and expired = 0 and
                        frozen = 0 and tick_lnk1 = 0 and disabled = 0)
         
           update order_fx set act_code = 'FXO_CCY', mkt_rate = @lst_bid

                where LTRIM(RTRIM(ord_stat)) in ('MISSED', '1', '2') AND
                  (mkt_rate <> @lst_bid and cur_1 = @cur_1 and cur_2 = @cur_2 and amount <> 0 and cancelled = 0 and executed =
                        0 and expired = 0 and
                  frozen = 0 and tick_lnk1 = 0 and disabled = 0) and
                  (ord_type = 'SLB' or
                  (ord_type = 'TP' and buy_cur = cur_1 and @fxo_cltsde = 0) or
                       (ord_type = 'TP' and buy_cur = cur_2 and @fxo_cltsde = 1) or
                  (ord_type = 'SL' and buy_cur = cur_1 and @fxo_cltsde = 1) or
                       (ord_type = 'SL' and buy_cur = cur_2 and @fxo_cltsde = 0))

           update order_fx set act_code = 'FXO_CCY', mkt_rate = @lst_ask

                where LTRIM(RTRIM(ord_stat)) in ('MISSED', '1', '2') AND
                  (mkt_rate <> @lst_ask and cur_1 = @cur_1 and cur_2 = @cur_2 and amount <> 0 and cancelled = 0 and executed =
                        0 and expired = 0 and
                  frozen = 0 and tick_lnk1 = 0 and disabled = 0) and
                  (ord_type = 'SLO' or
                  (ord_type = 'TP' and buy_cur = cur_1 and @fxo_cltsde = 1) or
                       (ord_type = 'TP' and buy_cur = cur_2 and @fxo_cltsde = 0) or
                  (ord_type = 'SL' and buy_cur = cur_1 and @fxo_cltsde = 0) or
                       (ord_type = 'SL' and buy_cur = cur_2 and @fxo_cltsde = 1))
         end  


Thank you in advance.
Sincerely,
Bob
0
Comment
Question by:jgroetch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 18041067
yes, using CASE construct:

   update order_fx set act_code = 'FXO_CCY'
      , mkt_rate = CASE
            WHEN ( LTRIM(RTRIM(ord_stat)) in ('MISSED', '1', '2') AND
                    (mkt_rate <> @lst_rate and cur_1 = @cur_1 and cur_2 = @cur_2 and ord_type = 'CALL' and cancelled = 0 and      
                               executed = 0 and expired = 0 and
                     frozen = 0 and tick_lnk1 = 0 and disabled = 0)
                 )
            THEN @lst_rate
            WHEN ( LTRIM(RTRIM(ord_stat)) in ('MISSED', '1', '2') AND
               (mkt_rate <> @lst_bid and cur_1 = @cur_1 and cur_2 = @cur_2 and amount <> 0 and cancelled = 0 and executed =
                        0 and expired = 0 and
               frozen = 0 and tick_lnk1 = 0 and disabled = 0) and
               (ord_type = 'SLB' or
               (ord_type = 'TP' and buy_cur = cur_1 and @fxo_cltsde = 0) or
                    (ord_type = 'TP' and buy_cur = cur_2 and @fxo_cltsde = 1) or
               (ord_type = 'SL' and buy_cur = cur_1 and @fxo_cltsde = 1) or
                    (ord_type = 'SL' and buy_cur = cur_2 and @fxo_cltsde = 0))
             )
            THEN @lst_bid
            WHEN ( LTRIM(RTRIM(ord_stat)) in ('MISSED', '1', '2') AND
               (mkt_rate <> @lst_ask and cur_1 = @cur_1 and cur_2 = @cur_2 and amount <> 0 and cancelled = 0 and executed =
                        0 and expired = 0 and
               frozen = 0 and tick_lnk1 = 0 and disabled = 0) and
               (ord_type = 'SLO' or
               (ord_type = 'TP' and buy_cur = cur_1 and @fxo_cltsde = 1) or
                    (ord_type = 'TP' and buy_cur = cur_2 and @fxo_cltsde = 0) or
               (ord_type = 'SL' and buy_cur = cur_1 and @fxo_cltsde = 0) or
                    (ord_type = 'SL' and buy_cur = cur_2 and @fxo_cltsde = 1))
              )
            THEN @lst_ask
        END
   where    ( LTRIM(RTRIM(ord_stat)) in ('MISSED', '1', '2') AND
                    (mkt_rate <> @lst_rate and cur_1 = @cur_1 and cur_2 = @cur_2 and ord_type = 'CALL' and cancelled = 0 and      
                               executed = 0 and expired = 0 and
                     frozen = 0 and tick_lnk1 = 0 and disabled = 0)
                 )
    OR       ( LTRIM(RTRIM(ord_stat)) in ('MISSED', '1', '2') AND
               (mkt_rate <> @lst_bid and cur_1 = @cur_1 and cur_2 = @cur_2 and amount <> 0 and cancelled = 0 and executed =
                        0 and expired = 0 and
               frozen = 0 and tick_lnk1 = 0 and disabled = 0) and
               (ord_type = 'SLB' or
               (ord_type = 'TP' and buy_cur = cur_1 and @fxo_cltsde = 0) or
                    (ord_type = 'TP' and buy_cur = cur_2 and @fxo_cltsde = 1) or
               (ord_type = 'SL' and buy_cur = cur_1 and @fxo_cltsde = 1) or
                    (ord_type = 'SL' and buy_cur = cur_2 and @fxo_cltsde = 0))
             )
  OR    (  LTRIM(RTRIM(ord_stat)) in ('MISSED', '1', '2') AND
               (mkt_rate <> @lst_ask and cur_1 = @cur_1 and cur_2 = @cur_2 and amount <> 0 and cancelled = 0 and executed =
                        0 and expired = 0 and
               frozen = 0 and tick_lnk1 = 0 and disabled = 0) and
               (ord_type = 'SLO' or
               (ord_type = 'TP' and buy_cur = cur_1 and @fxo_cltsde = 1) or
                    (ord_type = 'TP' and buy_cur = cur_2 and @fxo_cltsde = 0) or
               (ord_type = 'SL' and buy_cur = cur_1 and @fxo_cltsde = 0) or
                    (ord_type = 'SL' and buy_cur = cur_2 and @fxo_cltsde = 1))
                )
0
 
LVL 1

Author Comment

by:jgroetch
ID: 18046011
That's brilliant, mate. Thank you.
I've seen the Case statement embedded in an update but couldn't implement in this situation.

So if I understand correctly, each of the three conditions are embedded in the WHEN clause then they are repeated in the WHERE clause in order to optimize the complete Update statement. Is that right ?

I will try this in our test system and get back to you later. It looks like a winner so just be patient ... then I'll award the points.

Good day.
Bob
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18046168
yes, you understood correctly.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question