Update a column in a table with all possible combinations of values within a single column in SQL

Question

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.

Answer 1

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