Recently, I ran into a problem with an UPDATE statement. Here is the way the update process was built:
with CTE_1 as (...), CTE_2 as (...) update TargetTable set Value_1 = case c.TransType when 1 then c.ComputedAmt else Value_1, Value_2 = case c.TransType when 2 then c.ComputedAmt else Value_2 from CTE_x c where TargetTable.Key = c.Key
My intention was simple: update one column when the transaction type was one value, and update another column when the transaction type was another value. But, it failed. Let's take a look at the data generated in the CTE section for table CTE_x (that being either CTE_1 or CTE_2):
| Key | TransType | ComputedAmt |
|---|---|---|
| ABC01 | 1 | 123.45 |
| ABC01 | 2 | 989.02 |
| EFG33 | 1 | 34.00 |
| EFG33 | 2 | 3024.63 |
Now take a look at how TargetTable is populated:
| Key | Value_1 | Value_2 |
|---|---|---|
| ABC01 | 0.00 | 0.00 |
| EFG33 | 0.00 | 0.00 |
The problem with the UPDATE statement above is that I want to update each row in the target table twice. This is forbidden.
The solution was to add another CTE that took the data from CTE_x and pivoted it so that I would have a one-to-one relationship between the target table and the source of the updates:
with ... , CTE_pivot as ( select Key as "SourceKey", [1] as "VAL_a",[2] as "VAL_b" from (select Key,TransType,ComputedAmt from CTE_x) as x pivot ( max(ComputedAmt) for TransType in ([1],[2]) ) as pvt )
With the pivoted data now in CTE_pivot, the data was correctly organized thusly:
| Key | VAL_a | VAL_b |
|---|---|---|
| ABC01 | 123.45 | 989.02 |
| EFG33 | 34.00 | 3024.63 |
And with this, the final UPDATE statement gets a single row with both columns from the source table (now CTE_pivot):
with ... update TargetTable set Value_1 = VAL_a, Value_2 = VAL_b from CTE_pivot where CTE_pivot.Key = TargetTable.Key
