CODEWITHSUNDEEP
sqloracle-databaseoracle11gaggregate-functions
user2008558
user2008558

Reputation: 341

Populate null values with existing values within a unique group

I have this table whose null values need to be populated using values from val2 within a group (val1). However, I want to populate these nulls while taking into consideration name1 and/or name2 fields.

HAVE

val1 val2  name1 name2
1    abc    Jon   Doe
1   (null)  Jony  Doe
1   bde     Jony  Doe
1   abc     Jon   Doe
2   x1y2    Jony  Ronald
2   x1y2    Jony  Ronald
2  (null)   Jony  Ronald 
2  (null)   Jony  Ronald



WANT

val1 val2  name1 name2
1   abc     Jon   Doe
1   bde     Jony  Doe
1   bde     Jony  Doe
1   abc     Jon   Doe
2   x1y2    Jony  Ronald
2   x1y2    Jony  Ronald
2   x1y2    Jony  Ronald 
2   x1y2    Jony  Ronald

Upvotes: 0

Views: 259

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

One option would be using MAX(val2) OVER (PARTITION BY name1, name2 ORDER BY ...) analytic function whenever values of the column val2 are null :

SELECT val1,
       NVL(val2,MAX(val2) OVER (PARTITION BY name1, name2 ORDER BY 0)) AS val2,
       name1, name2
  FROM t

Demo

Upvotes: 1

The Impaler
The Impaler

Reputation: 48750

You can use a correlated subquery to retrieve the values you need when it's null.

For example:

update my_table a
set val2 = (
  select max(b.val2) 
  from my_table b 
  where b.name1 = a.name1 and b.name2 = a.name2
)
where val2 is null

Upvotes: 1

Related Questions