CODEWITHSUNDEEP
sqloracle-database
TheSchmidly
TheSchmidly

Reputation: 59

How do I replace blank spaces with null values and query where values are not null? (I'm filling in a table)

I have two tables:

table1:

USER_NUM FIRST_NAME LAST_NAME
A123 Billy Bob
A124 Billy Joe
Jane Doe
John Doe

I am trying to insert FIRST_NAME and LAST_NAME under USER_NAME into table2:

OWNER_ID USER_NUM USER_NAME
111 A123
112 A124

Using this query:

BEGIN
  FOR c1 IN (SELECT UPPER(t1.USER_NUM) number
                    ,t1.FIRST_NAME || ' ' || LAST_NAME user_name
               FROM table1 t1
              INNER JOIN table2 t2
                 ON number = t2.USER_NUM
              WHERE regexp_replace(number, '[[:space:]]+', NULL) IS NOT NULL
             )
  LOOP
    INSERT INTO table2 (USER_NAME) values (c1.user_name)
  END LOOP;
END;

The problem is, if I isolate my select statement I still get blank values for USER_NUM with a table looking like this:

USER_NUM USER_NAME
A123 Billy Bob
A124 Billy Joe
Jane Doe
John Doe

and I get an error saying I can't insert null values into my table.

How do I get rid of these blank values in USER_NUM?

Upvotes: 1

Views: 361

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You want update:

update table2 t2
    set user_name = (select t1.FIRST_NAME || ' ' || t1.LAST_NAME
                     from table1 t1
                     where t1.user_num = t2.user_num
                    )
    where exists (select 1
                  from table1 t1
                  where t1.user_num = t2.user_num
                 );

Upvotes: 2

Related Questions