Using a Case statement to update another table

Question

I have a table with one record in it. I need to update 8 fields in another table based on the TYPE column of the one record. I thought a Case statement was the way to go, but I am pulling my hare out! I have searched and seen some examples that come close BUT not quite! any help please! here is my code.

UPDATE [dbo].[PART_DIMENSION]

SELECT [SELL_PACK],
    CASE q.PACK_TYPE WHEN 'SELL' then 1,
              [SELL_PACK_UOM] = Q.PACK_ASSMBY,
              [SELL_PACK_QTY] = Q.QTY,
              [SELL_PACK_LENGTH] = Q.LENGTH,
              [SELL_PACK_WIDTH] = Q.WIDTH,
              [SELL_PACK_HEIGHT] = Q.HEIGHT,
              [SELL_PACK_WEIGHT] = Q.WEIGHT,
              [SELL_PACK_INNER_QTY] = Q.INNER_QTY,
              [SELL_PACK_CAN_NEST] = Q.CAN_NEST,
              [SELL_PACK_NEED_RETAIL_PACK] = Q.NEED_RETAIL_PACK
            FROM PART_DIMENSION as a
            INNER JOIN Qubiscan as q
            on a.part_id=q.part_id

    WHEN q.PACK_TYPE='INNER' then
           SET[INNER_PACK]  = 1,
              [INNER_PACK_UOM] = Q.PACK_ASSMBY,
              [INNER_PACK_QTY] = Q.QTY,
              [INNER_PACK_LENGTH] = Q.LENGTH,
              [INNER_PACK_WIDTH] = Q.WIDTH,
              [INNER_PACK_HEIGHT] = Q.HEIGHT,
              [INNER_PACK_WEIGHT] = Q.WEIGHT
            FROM PART_DIMENSION as a
            INNER JOIN Qubiscan as q
            on a.part_id=q.part_id

    WHEN q.PACK_TYPE='MASTER' then
           SET[MASTER_PACK] = 1,
              [MASTER_PACK_UOM] = Q.PACK_ASSMBY,
              [MASTER_PACK_QTY] = Q.QTY,
              [MASTER_PACK_LENGTH] = Q.LENGTH,
              [MASTER_PACK_WIDTH] = Q.WIDTH,
              [MASTER_PACK_HEIGHT] = Q.HEIGHT,
              [MASTER_PACK_WEIGHT] = Q.WEIGHT
            FROM PART_DIMENSION as a
            INNER JOIN Qubiscan as q
            on a.part_id=q.part_id

Answer 1

Getting everything done in one shot is a good goal, but sometimes you can try to do too much at once. The CASE may not be the best choice in this circumstance, because doing what you want would really require about 24 CASE statements...doable, but I'm not sure how readable it would be. I'd suggest breaking this down into some simpler chunks first..consider this (hopefully with minimal typos :) ):

Update a
  from part_dimension a
  join qubiscan q
    on a.part_id=q.part_id
   set           a.[MASTER_PACK] = 1,
              a.[MASTER_PACK_UOM] = Q.PACK_ASSMBY,
              a.[MASTER_PACK_QTY] = Q.QTY,
              a.[MASTER_PACK_LENGTH] = Q.LENGTH,
              a.[MASTER_PACK_WIDTH] = Q.WIDTH,
              a.[MASTER_PACK_HEIGHT] = Q.HEIGHT,
              a.[MASTER_PACK_WEIGHT] = Q.WEIGHT
where q.pack_type='MASTER'

Repeat this block for the other two field lists/pack_types.