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

Question

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?

Gordon Linoff · Accepted Answer · 2021-06-16 15:06:40Z

2

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
                 );

answered Jun 16, 2021 at 15:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related