1

How can I update a column in a table with a value from other column for all possible combinations ?

Example:

COL1 |  COL2 |  COL3 | ClientKey  | MasterKey
-----+-------+-------+------------+------------     
123  |  345  |  678  |  AAA       |   AAA
N/A  |  345  |  678  |  BBB       |   AAA
N/A  |  N/A  |  678  |  CCC       |   AAA
N/A  |  345  |  N/A  |  DDD       |   AAA
123  |  345  |  N/A  |  EEE       |   AAA
123  |  N/A  |  678  |  FFF       |   AAA

First row has all values populated and it has a key (ClientKey). How do I update MasterKey column with ClientKey column checking for all possible combinations that can occur with the first row. 'N/A' value is equivalent to NULL value.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Kiran Mudumbi
  • 13
  • 3
  • What is your expected output after the UPDATE action? – mkRabbani Aug 25 '19 at 05:09
  • @mkRabbani MasterKey column in the above table is the expected output after the UPDATE action. MasterKey need to be updated with ClientKey for all possible combinations of first 3 columns. – Kiran Mudumbi Aug 25 '19 at 15:07

1 Answers1

0

Assuming I understand the question, the easiest way would be to use an update statement with a self join:

UPDATE t1
SET MasterKey = t0.ClientKey
FROM Table as t0
INNER JOIN Table as t1
    ON  (t0.Col1 = t1.Col1 OR t1.Col1 IS NULL)
    AND (t0.Col2 = t1.Col2 OR t1.Col2 IS NULL)
    AND (t0.Col3 = t1.Col3 OR t1.Col3 IS NULL)
WHERE t0.Col1 IS NOT NULL
AND t0.Col2 IS NOT NULL
AND t0.Col3 IS NOT NULL
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • Excellent. I was thinking on the grounds of creating temporary table with all master keys and updating the column. Your approach is more simpler. Now if I want to update another column say IsDeleted to 0 for the first row and rest of the rows to 1 would I eliminate the WHERE condition and retain the JOIN condition ? Thanks. – Kiran Mudumbi Aug 25 '19 at 15:02
  • You just have to add a `case` expression to the update statement: `IsDeleted = case when coalesce(t1.col1, t1.col2, t1.col3) is null then 1 else 0 end` The coalesce function will return the first non-null argument, or null if all arguments are null. – Zohar Peled Aug 25 '19 at 15:07
  • Sorry I jumped ahead a little bit but the IsDeleted logic didn't work. All the rows have IsDeleted value of 0 and none are updated to 1. Do I need a separate update statement to handle IsDeleted logic ? – Kiran Mudumbi Aug 26 '19 at 16:32