Solved

Need help with optimizing an Sql Update statement

Posted on 2006-11-29
3
415 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
  • 2
3 Comments
 
LVL 142

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 142

Expert Comment

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

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

707 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now