Initialize to some data, if data is not present in certain fields

Question

I want to put some same value in some fields where data is not present, for that do I need to query each field and see if there is data present or not and accordingly put the data, or there is some other work arround.

Like

Name    Age    City
N        22      J
K                K
          23     L

Here I want to put data on those fields which don't have data otherwise I don't want to touch tghose fields. After inserting it should look like

Name    Age    City
N        22      J
K        Gar     K
Gar      23      L

There is no validation for the datatype, all are of same datatype. How should I write the query in that case..

Answer 1

select NVL(name, 'Gar'), NVL(age, 'Gar'), city from your_table;

Answer 2

If you just want to do this for all rows in the table you can do something like

UPDATE your_table
  SET name = NVL(name, 'Gar'),
      age = NVL(age, 'Gar');

This assumes that 'age' is a character field.

Share and enjoy.

Answer 3

Do you thing this?

create test table...

create table test_table(Name VARCHAR2(10), Age NUMBER(10), City VARCHAR2(10));

insert sample data...

insert into test_table(Name, Age, City) values('N', 22, 'J');
insert into test_table(Name, Age, City) values('K', NULL, 'K');
insert into test_table(Name, Age, City) values(NULL, 23, 'L');
commit;

data look like this...

SQL> select * from test_table;

NAME              AGE CITY
---------- ---------- ----------
N                  22 J
K                     K
                   23 L

and finally programmatically update sample data using CASE statement...

update test_table
  set Name = (CASE WHEN Name IS NULL AND Age >= 23 THEN 'John' ELSE Name END)
     ,Age = (CASE WHEN Age IS NULL AND Name = 'K' THEN 21 ELSE Age END)
;
commit;

3 rows updated

and now data look like this...

SQL> select * from test_table;

NAME              AGE CITY
---------- ---------- ----------
N                  22 J
K                  21 K
John               23 L