CODEWITHSUNDEEP
sqloracle-database
Diana Amza
Diana Amza

Reputation: 303

Copying data from a column to another and handling null cases

I'm transferring some data from one column to another and I'm not sure how to cover the case when the first column is nullable and the second one isn't.

COLUMN1 has some values or null, and I want to copy those values in COLUMN2, and get rid of the null values. How can I cover null cases in the best way? That is, if I have null in COLUMN1, I want to mark it with a certain value, eg 0 in COLUMN2. I want COLUMN2 to have a non-null constraint. The rest of the values will just be copied.

Basically this but in a way that would work with null as well:

INSERT INTO table (COLUMN2) SELECT COLUMN1 FROM table;

Any ideas are appreciated!

Thanks!

Upvotes: 1

Views: 931

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think you want update, not insert:

update table
    set column2 = coalesce(column1, 0);

EDIT:

If your insert statement is really what you want to do, then you can put the logic there:

INSERT INTO table(COLUMN2)
    SELECT coalesce(COLUMN1, 0)
    FROM table;

Upvotes: 1

Related Questions