New to sql, how to insert column values into new column?

Question

What I have:

ALTER TABLE countryb
ADD gnppercap real
;


INSERT INTO countryb (gnppercap)
    SELECT gnp/population
    FROM countryb
;

I successfully created the column "gnppercap", now I want to populate values in every row with the variable. The new variable is the product of gnp and 1/population, with variables gnp and population already in the table I'm altering, countryb.

Here's the error:

ERROR: null value in column "code" violates not-null constraint
SQL state: 23502
Detail: Failing row contains (null, null, null, null, null, null, null, null, null, null, null, >null, null, null, null, 0.000263028).

I know that the table, countryb has a ton of non-null vars in it, so that's what those nulls are, I think. I thought that since I specified the column I am inserting values into, it wouldn't matter...?

I'm lost. Help appreciated!

You added a column. It got whatever default value it should. Now you want to change this value in already existing row. — PM 77-1
– PM 77-1, Commented May 16, 2013 at 17:31

CharlesC · Accepted Answer · 2013-05-16 17:29:11Z

3

You want to update the table, not insert a new row.

update countryb set gnppercap = gnp/population

As an aside: You probably don't want to store a calculated value in a separate column. What happens if you update gnp or population? Your gnppercap column will be inaccurate.

answered May 16, 2013 at 17:29

CharlesC

3503 silver badges8 bronze badges

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

2 Comments

Jefftopia Over a year ago

Wow yeah that was too easy, can't believe I missed it. Another question, sometimes the values are 0 in the numerator and denominator, I want to set these equal to null. How can I do that?

CharlesC Over a year ago

That would give you a null gnppercap. Your new column is probably null by default so there is no need to re-update that value to null. I would do this instead: update countryb set gnppercap = gnp/population where gnp <> 0 and population <> 0

Collectives™ on Stack Overflow

New to sql, how to insert column values into new column?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related