CODEWITHSUNDEEP
sql
puncherello
puncherello

Reputation: 13

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

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

Upvotes: 1

Views: 1201

Answers (3)

Cihan Özgür Balbay
Cihan Özgür Balbay

Reputation: 1

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;

Upvotes: 0

sia
sia

Reputation: 577

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

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

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

Upvotes: 3

Related Questions