replace null values by non-null values in same column based on values in another column-CodePudding

I have a table with the following:

  |    A    |    B    |  
  |  blue   |  null   |
  |  orange |  null   | 
  |  orange |   x     | 
  |  blue   |   y     |

I would like to achieve this:

  |    A    |    B    |    C    |
  |  blue   |  null   |    y    |
  |  orange |  null   |    x    |
  |  orange |   x     |    x    |
  |  blue   |   y     |    y    |

The only values that orange can take in column B are null or x, same for blue (null or y) Apologies if this has already been answered, I couldn't find it anywhere on the site.

Thanks

CodePudding user response：

Assuming you have analytic functions available, you may simply try:

SELECT A, B, MAX(B) OVER (PARTITION BY A) AS C
FROM yourTable;

We could also use an aggregation approach:

SELECT t1.A, t1.B, t2.MAX_B AS C
FROM yourTable t1
INNER JOIN
(
    SELECT A, MAX(B) AS MAX_B
    FROM yourTable
    GROUP BY A
) t2
    ON t2.A = t1.A

CodePudding user response：

above answer would work. I added my solution:

SELECT c1.A, c1.B, c2.B
FROM colors c1
INNER JOIN 
(SELECT DISTINCT A, B
 FROM colors 
 where B is NOT NULL) c2
ON c1.A =  c2.A