I have this small table having duplicate id and names and I want to replace null values with names u-CodePudding

There is some null values at name column and I want to relace null to names matching with ID

I got it with self join:

select a.id, a.name, b.id, b.name, nvl(a.name,b.name)
from names a
join names b
on a.id = b.id
where a.name is null and b.name is not null;

But don't no how do I update it in the table. I tried update statement but did not work

This is what output look like []

CodePudding user response：

Try using a conditional update with WHERE:

UPDATE names
SET name = id
WHERE name IS NULL

CodePudding user response：

Problem is that there are no unique [id, name] combinations in rows that don't have empty name columns.

SQL> select * From test;

        ID NAME
---------- ------
         1 naya
         1 naya
         2 amar
         3 dhruv           --> here
         4 shyla
         4 shyla
         3 diya            --> here
         5 ananya
         6 ishaan
         5
         2
         2
         1
         3

14 rows selected.

SQL>

So, which name would you want to get for ID = 3? You can't have both, so - one option is to select any, e.g. max.

SQL> update test a set
  2    a.name = (select max(b.name)
  3              from test b
  4              where b.id = a.id
  5             )
  6  where a.name is null;

5 rows updated.

Result:

SQL> select * From test;

        ID NAME
---------- ------
         1 naya
         1 naya
         2 amar
         3 dhruv
         4 shyla
         4 shyla
         3 diya
         5 ananya
         6 ishaan
         5 ananya
         2 amar
         2 amar
         1 naya
         3 diya

14 rows selected.

SQL>

CodePudding user response：

From Oracle 12, you can use:

UPDATE names n
SET    name = (SELECT name
               FROM   names x
               WHERE  x.id = n.id
               ORDER BY name NULLS LAST
               FETCH FIRST ROW ONLY)
WHERE  name IS NULL

db<>fiddle here