updating one table from another with complex conditions

Question

Thank you for your help in advance. I am looking for a neat solution for the following update scenario:

• I have a table TableA with 5 columns (KeyCol, colA, ColB, ColC, ColD)
• I have another table Table2 with 2 columns (KeyCol, AvgCol)

I want to do something like this:

update AvgCol in table2, joining on KeyCol

from this logic:

• if colA and ColB both have Non NUll values, calculate avg of COlA and COlB and store in tempA, else choose coalesce (Cola / colb) in TempA, if both are nUll, NULL in TempA

• if colC and ColD both have Non NUll values, calculate avg of COlC and COlD and store in tempB, else choose coalesce (ColC / colD) in TempB, if both are nUll, NULL in TempB

• if TempA and TempB both have Non NUll values, calculate avg of TempA and TempB and store in Table2.AvgCol, else choose coalesce (TempA / TempB), if both are nUll, NULL

Answer 1

SELECT Table2.keycol AS KEY
       CASE
              WHEN colA   IS NULL
              AND    colb IS NULL
              THEN NULL
              WHEN colA   IS NOT NULL
              AND    colb IS NOT NULL
              THEN (colA + colB)/2
              ELSE COALESCE(colA,colB)
       END AS TempA,
       CASE
              WHEN colC   IS NULL
              AND    cold IS NULL
              THEN NULL
              WHEN colC   IS NOT NULL
              AND    cold IS NOT NULL
              THEN (colC + cold)/2
              ELSE COALESCE(colc,colc)
       END AS Tempb
FROM
ON (
              Table2.keycol = Table1.keycol
       )
UPDATE Table2
SET    AVG =
       CASE
              WHEN TempA   IS NULL
              AND    Tempb IS NULL
              THEN NULL
              WHEN TempA   IS NOT NULL
              AND    Tempb IS NOT NULL
              THEN (TempA + Tempb)/2
              ELSE COALESCE(TempA,Tempb)
       END
FROM
FROM   #temp innner
       JOIN Table2
       ON     Table2.keycol=#temp.key

Answer 2

You should use case

UPDATE TableB
SET AvgCol=
case when TempA is null and TempB is null then Null else 
case when TempA is not null and TempB is not null then (TempA+TempB)/2 else
coalesce(TempA, TempB) end  end 
FROM (select KeyCol,
    case when ColA is null and ColB is null then Null else 
    case when ColA is not null and ColB is not null then (ColA+ColB)/2 else
    coalesce(ColA, ColB) end  end as TempA,
    case when ColC is null and ColD is null then Null else 
    case when ColC is not null and ColD is not null then (ColC+ColD)/2 else
    coalesce(ColC, ColD) end  end as TempB
    FROM TableA) TEMP
INNER JOIN TableB
on TEMP.KeyCol=TableB.KeyCol

Answer 3

I think this will do it. I'm using isnull and swapping the order of the columns in each isnull, so that if only one column of the pair is null, the calculation becomes essentially (X+X)/2 which is just X. In other words, if only one of the pair is null, the non-null value is returned. If both are null, the entire calculation will return null.

;with cteTemp as (
    select keycol,
           (isnull(ColA, ColB) + isnull(ColB, ColA))/2 as TempA,
           (isnull(ColC, ColD) + isnull(ColD, ColC))/2 as TempB
        from TableA
)
update t2
    set AvgCol = (isnull(t1.TempA, t1.TempB) + isnull(t1.TempB, t1.TempA))/2
    from cteTemp t1
        inner join Table2 t2
            on t1.keycol = t2.keycol

Answer 4

try this on for size

update Table2
set AvgCol= case when TempA+TempB is null then coalesce(TempA,TempB) else (TempA+TempB)/2 end
from
    Table2 b
        inner join 
(
    select
        case when ColA+ColB is null then coalesce(ColA,ColB) else (ColA+ColB)/2 end as TempA,
        case when ColC+ColD is null then coalesce(ColC,ColD) else (ColC+ColD)/2 end as TempB,
        a.KeyCol
    from TableA a
        inner join Table2 b
            on a.KeyCol=b.KeyCol
) a
    on B.KeyCol=a.KeyCol

When you add a number to null, the result is null. coalesce, if both values are null, will return null.

Answer 5

Using COALESCE exclusively can greatly reduce your code overhead:

UPDATE t2
SET AvgCol = COALESCE( (tempA + tempB) / 2, tempA, tempB)
FROM @table2 t2 
INNER JOIN (
  SELECT
    KeyCol, 
    COALESCE( (colA + colB) / 2, colA, colB) AS tempA,
    COALESCE( (colC + colD) / 2, colC, colD) AS tempB
  FROM @tableA
) tA ON t2.KeyCol = tA.KeyCol

Answer 6

Just for fun, there are already enough other solutions here:

declare @TempA float, @TempB float

update b set 
  @TempA = coalesce((a.ColA+a.ColB)/2, a.ColA, a.ColB)
, @TempB = coalesce((a.ColC+a.ColD)/2, a.ColC, a.ColD)
, AvgCol = coalesce((@TempA+@TempB)/2, @TempA, @TempB)
from TableA a
join Table2 b on a.KeyCol = b.KeyCol