Solved

Need help with optimizing an Sql Update statement

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

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.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

860 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