Solved

Need help with optimizing an Sql Update statement

Posted on 2006-11-29
3
417 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL: Episode III - Revenge of The Dude 24 53
Help Required 3 96
date diff with Fiscal Calendar 4 30
Syntax for query to update table 2 11
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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.

773 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