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

Question

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

This could be a simple join, except that there are cases you didn't show or mention. What if orange has another row 'orange', 'z' ? Which value 'x' or 'z' do you want to show in the orange row containing null? — Jon Armstrong
– Jon Armstrong, Commented Oct 16, 2021 at 12:53

Tim Biegeleisen · Accepted Answer · 2021-10-16 12:53:00Z

3

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

answered Oct 16, 2021 at 12:53

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sia · Accepted Answer · 2021-10-16 15:18:00Z

0

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

answered Oct 16, 2021 at 15:18

sia

5891 gold badge9 silver badges24 bronze badges

Comments

Cihan Özgür Balbay · Accepted Answer · 2022-07-30 11:03:48Z

0

I wanted to ask here because there is a similar situation. my code is as below. but my problem is that my query is still not finished after 15 hours since my file is quite large. Maybe this is normal for SQL or I should optimize my query?

(3.541.265 rows - PostgreSQL)

UPDATE clean_table_v01 AS t1
SET start_station_name = (
    SELECT start_station_name
    FROM clean_table_v01 AS t2
    WHERE t1.start_station_id = t2.start_station_id
      AND start_station_name NOTNULL
    LIMIT 1)
WHERE start_station_name ISNULL;

edited Jul 30, 2022 at 11:03

answered Jul 30, 2022 at 10:31

Cihan Özgür Balbay

12 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related