Populate null values with existing values within a unique group

Question

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

Barbaros Özhan · Accepted Answer · 2020-06-27 11:59:27Z

1

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

answered Jun 27, 2020 at 11:59

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

Comments

The Impaler · Accepted Answer · 2020-06-27 11:16:42Z

1

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

answered Jun 27, 2020 at 11:16

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Collectives™ on Stack Overflow

Populate null values with existing values within a unique group

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related