CASE Simplifies SQL Update

I need to run a series of similar updates against 12 groups in a very large physical file (millions of records). Is there a way I can combine the updates into one SQL update command? I’d rather have the system read the file once, not 12 times.

–Jim

Here’s an example that illustrates Jim’s update. Set the minimum order quantity to 12 for all items in class A1.

update items
   set minordqty = 12
 where class = 'A1'

If Jim has 12 class values, he has to run 12 update commands. However, if he were to use a CASE structure, he would only need one update command. To illustrate, here’s the same update with five classes.

update items
   set minordqty =
          case class
             when 'A1' then  12
             when 'B1' then 144
             when 'B2' then 144
             when 'D1' then  24
             when 'D2' then  12
             else 12
          end

Case opens up some powerful opportunities. Here’s another example.

Suppose you need two updates to the item master file. You want to update the minimum order quantity using one set of criteria, and update the discount code using an unrelated set of criteria. You could run two SQL update commands, each with its own where clause, but try this instead.

update items
  set minordqty =
         case class
            when 'A1' then  12
            when 'B1' then 144
            when 'B2' then 144
            when 'D1' then  24
            when 'D2' then  12
            else 12
         end,
      discount =
         case
            when MakeBuy = 'M' then 1
            when VMI = 'Y'     then 3
            else 2
         end

Of course, there’s no reason you can’t use a where clause too, if it suits your purposes. Here’s the previous query, but it only updates records that are marked as active.

update items
  set minordqty =
         case class
            when 'A1' then  12
            when 'B1' then 144
            when 'B2' then 144
            when 'D1' then  24
            when 'D2' then  12
            else 12
         end,
      discount =
         case
            when MakeBuy = 'M' then 1
            when VMI = 'Y'     then 3
            else 2
         end
 where active = 'Y'

–Ted

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot