• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 427
  • Last Modified:

Need help with optimizing an Sql Update statement

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
jgroetch
Asked:
jgroetch
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
jgroetchAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, you understood correctly.
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now