CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.2
Barbara Lokes
Barbara Lokes

Reputation: 31

How to insert values into the rows of a newly added column of a table in PostgreSQL

I imported a personal geodatabase into PostgreSQL. For one of my tables, I added a new column. This column is empty and I want to add in/insert values into this column so that all its rows will be populated. When I used: 

INSERT INTO district (province_id) VALUES
('13'), ('13'), ('13'), ('13'), ('13'),  ('13'), ('12'), ('12'), ('12'), ('12'), ('12'), ('12'), ('12'), ('12'), ('12'), ('19'), ('19');

The result is such that new rows were created, so instead of having 17 rows, I ended up having 34 rows. When I try to use UPDATE...Set...=... Where...., the query ran successfully, however, no row was affected. My question now is: how can I add the values for each of the rows in the newly created column such that no new rows will be created as a result?

Thanks in advance and any help would be very much appreciated.

Barbaara

Upvotes: 3

Views: 29645

Answers (1)

Ihor Romanchenko
Ihor Romanchenko

Reputation: 28641

First - in the table "district" you must choose a column, that is unique for every record in "district" (a primary key. I guess it is called "district_id").

Then for every "district_id" you need to set the "province_id" :

UPDATE district 
SET province_id = 13 -- put your province_id value here
WHERE district_id = 1234; -- put the key value here

It will search for a record in "district" with "district_id" = 1234 and SET its "province_id" to 13.

Next you must write such UPDATE's for every district or write something like:

WITH vals(district_id numeric, province_id numeric) as 
(    VALUES((1234,13), -- pairs of district_id , province_id
            (1235,13),
            (1236,13),
            ........)  -- write the pair for every district
)
UPDATE district as dist
SET province_id = vals.province_id
FROM vals
WHERE dist.district_id = vals.district_id

Upvotes: 4

Related Questions